I have vb.net application with SQL server express.
Here is my code to save data to database, but itn not working.
Here ID is not the primary key.

Imports System.Data.SqlClient
Imports System.Data

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim cmd As New SqlCommand
        Dim con As New SqlConnection("Data Source=SIS-JES4MPANRS0\SQLEXPRESS;Initial Catalog=Pathology;Integrated Security=True")
        con.Open()

        Dim cmdlogin As New SqlCommand(("SELECT ID FROM Accessonbook-1"), con)
        Dim dalogin As New SqlDataAdapter
        Dim dslogin As New DataSet
        dalogin.SelectCommand = cmdlogin
        cmdlogin.ExecuteNonQuery()
        dslogin.Clear()
        dalogin.Fill(dslogin, "Accessonbook-1")



        If dslogin.Tables(0).Rows.Count > 0 Then
            Dim i As Integer = 0

            While i < dslogin.Tables(0).Rows.Count
                If IDtxt.Text = dslogin.Tables(0).Rows(i).Item(0).ToString Then

                    MsgBox("ID Already exist")
                    GoTo Line1
                Else

                End If
                i = i + 1
            End While


        Else
            MsgBox("No Data Exist")
        End If

        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "insert into Accessonbook-1 (ID,Name,Age,Sex) Values ('" & IDtxt.Text & "','" & Nametxt.Text & "','" & Agetxt.Text & "','" & Sextxt.Text & "')"

        cmd.ExecuteNonQuery()
        MsgBox("Added Successfuly")

Line1:

    End Sub

Try below code to insert your records...

Dim sql As String = "INSERT INTO [Accessonbook-1] (ID,Name,Age,Sex) VALUES (@q1, @q2, @q3, @q4)"

    Using cn As New SqlConnection("Your connection string here"), _
          cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@q1", SqlDbType.VarChar, 50).Value = IDtxt.Text
        cmd.Parameters.Add("@q2", SqlDbType.VarChar, 50).Value = Nametxt.Text  
        cmd.Parameters.Add("@q3", SqlDbType.VarChar, 50).Value = Agetxt.Text
        cmd.Parameters.Add("@q4", SqlDbType.VarChar, 50).Value = Sextxt.Text

        cn.Open()
        cmd.ExecuteNonQuery()

Ok let me try
Thanks

To use sqldataAdapter, you atleast need one primary key.

You could try this:

cmd.Connection = con
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "insert into Accessonbook-1 (ID,..)VALUES(@IdNum,..)"
    .parameter.addwithvalue("@IdNum", IDtxt.Text).value.Tostrinf()
    .ExecuteNonQuery

.parameter.addwithvalue("@IdNum", IDtxt.Text).value.Tostring()

You can use SQLDataAdapter and SQLCommandBuilder. It will generate Insert, Delete and Update Command

cmd = New SQLCommand("Select * From table", con)

con.Open()
adpt = New SQLDataAdapter(cmd)
Dim builder As SQLCommandBuilder = New SQLCommandBuilder(adpt)
dtset = New DataSet()
adpt.Fill(dtset)
DataGridView1.DataSource = dtset.Tables(0).DefaultView
con.Close()

As for the save button.

 Try
            Me.Validate()
            Me.adpt.Update(Me.dtset.Tables(0))
            Me.dtset.AcceptChanges()
        Catch ex As Exception
            MsgBox("Please check your records")
        End Try
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.