so i tried learning visual studio (c#) and run to this problem that i genuinely can't get my way to fix this problem. Inserting data from visual studio form to database

my control

Imports System.Data.Odbc

Public Class ClsCtlPenumpang
Dim SQL As String

Public Function SIMPAN_DATAPenumpang(ByVal _pbl As ClsEntInfoPenumpang) As String
    Dim KP As String
    KP = ""
    TUTUPKONESI()
    With _pbl
        Sql = "CALL INSERTPENUMPANG('" & .Nama_penumpang & "')"
        MsgBox(Sql)
        Try
            DTA = New OdbcDataAdapter(Sql, BUKAKONEKSI)
            DTS = New DataSet
            DTA.Fill(DTS, "TABEL_KDPenumpang")
            KP = DTS.Tables("TABEL_KDPenumpang").Rows(0)(0).ToString

        Catch ex As Exception
            If _pbl Is Nothing Then
                _pbl = New ClsEntInfoPenumpang
            End If
        End Try
    End With
    TUTUPKONESI()

    Return KP
End Function


Function kodebaru() As String
    Dim baru As String
    Dim kodeakhir As Integer
    Try
        DTA = New OdbcDataAdapter("select max(right(id_penumpang,2))from info_penumpang", BUKAKONEKSI)
        DTS = New DataSet()
        DTA.Fill(DTS, "max_kode")
        kodeakhir = Val(DTS.Tables("max_kode").Rows(0).Item(0))
        baru = "SEAT" & Strings.Right("0" & kodeakhir   1, 2)
        Return baru
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Function
End Class

my entities

Public Class ClsEntInfoPenumpang
Private _id_penumpang As String
Private _nama_penumpang As String

Public Property Id_penumpang As String
    Get
        Return _id_penumpang
    End Get
    Set(value As String)
        _id_penumpang = value
    End Set
End Property

Public Property Nama_penumpang As String
    Get
        Return _nama_penumpang
    End Get
    Set(value As String)
        _nama_penumpang = value
    End Set
End Property
End Class

my form

 Public Class FormPenumpang
    txtNama.Focus()
    txtIDPenumpang.Text = KontrolPenumpang.kodebaru()
    txtIDPenumpang.Enabled = False

End Sub

Private Sub btnSave_Click_1(sender As Object, e As EventArgs) Handles btnSave.Click
    With EntitasPenumpang
        .Id_penumpang = txtIDPenumpang.Text
        .Nama_penumpang = txtNama.Text
    End With

    KontrolPenumpang.SIMPAN_DATAPenumpang(EntitasPenumpang)

    MdiParent = FormUtama
End Sub
End Class

the problem is in the control, can't add anything while the automatic numbering is working fine (yes my odbc is right, i can add/delete using other form, and the database i assume is correct)

CodePudding user response：

You are totally misusing that data adapter. You don't call Fill to insert data. Fill is to execute a SELECT statement and retrieve data into a DataTable. If you want to use a data adapter to insert data into a database using a stored procedure then you need:

1. A DataTable containing one or more DataRows with a RowState of Added.
2. A data adapter with its InsertCommand set to a command containing the name of the stored procedure in its CommandText, its CommandType set to StoredProcedure and appropriate its Parameters populated appropriately.
3. To call Update on the data adapter and pass the DataTable as an argument.

Generally speaking, you use the same data adapter to retrieve data into a DataTable and then save changes from that DataTable back to the database. Here's one I prepared earlier:

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private table As New DataTable
 
Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
 
    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
 
    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    Me.adapter.DeleteCommand = delete
    Me.adapter.InsertCommand = insert
    Me.adapter.UpdateCommand = update
 
    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
 
Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)
 
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub
 
Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
End Sub

That example uses SqlClient to connect to SQL Server, which you should do if that's the database you're using. There are dedicated ADO.NET providers for various other databases too, which you should generally use in preference to ODBC. This example also uses inline SQL. As I said, if you are using a stored procedure, replace the SQL code with the stored procedure name - JUST the name - and then set the CommandType of the command to System.Data.CommandType.StoredProcedure.

CodePudding user response：

This question was hard to decipher but I think the problem is:

Sql = "CALL INSERTPENUMPANG('" & .Nama_penumpang & "')"

i genuinely can't get my way to fix this problem. Inserting data from visual studio winform to database

You're trying to insert data to the database and you're trying to call a Stored Procedure named INSERTPENUMPANG.

In Sql Server the way to execute Stored Procedures is EXEC

Solution:

Sql = "EXEC INSERTPENUMPANG('" & .Nama_penumpang & "')"