locked
Adding data into SQL database in vb RRS feed

  • Question

  • Hello,

    I am developing a point of sale application, I need to add data to the database but I am unable to do it. The code executes but jumps over "adaptor.Fill(dataset, "0")" to the "Catch ex As Exception". Please need help on this problem. Thank you.

    THIS IS THE CODE:

    Try
                Dim connection As New SqlClient.SqlConnection
                Dim command As New SqlClient.SqlCommand
                Dim adaptor As New SqlClient.SqlDataAdapter
                Dim dataset As New DataSet
                connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataBase.mdf;Integrated Security=True;User Instance=True")
                command.CommandText = "INSERT into [Users] WHERE UserName='" & txtNusername.Text & "' AND Passwords='" & txtNpassword.Text & "';"
                connection.Open()
                command.Connection = connection
                adaptor.SelectCommand = command
                adaptor.Fill(dataset, "0")
                Dim count = dataset.Tables(0).Rows.Count
                If count > 0 Then
                    MsgBox(txtNusername.Text & "Added System Users", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information, "Add New User")
                   End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
            End Try


    ga

    Wednesday, July 24, 2013 7:16 AM

Answers

  • DONT USE ADAPTER OR DATASET ,it is unnecessary. pls check the database whether the record has been updated or not.
    • Marked as answer by Ageorgia Wednesday, July 24, 2013 3:38 PM
    Wednesday, July 24, 2013 7:56 AM
  • Hi,

    I would recommend to read the ADO.Net introduction on MSDN Library. You mixed up stuff completly!

    1) As Sridhar pointed out: There is no need to use a DataAdapter / DataTable to insert a record. Simply use the SqlConnection and SqlCommand to execute the SQl Statement.
    - Create the Connection
    - Create the Command
    - Execute the Command
    - Dispose Command and Connection (Please check the using statement! That is something that makes it straight forward and it is really easy to use!)

    2) You can of course use a DataTable / DataAdapter. But you have to understand that (And the MSDN Library describes it in detail!). In short the technology is used in this way:
    - DataSet / DataTable is an Offline copy of existing data. The great thing is, that it is not just storing the data but also the state (e.g. modified, added, deleted) and you can even access "old" values e.g. froma modified row you can read the original data.
    - The DataAdapter is simply a pattern that should help you to read / write data. So when you use the DataAdapter, you are doing this:
    a) You get an offline copy of the data. (The Fill method is used for this)
    b) You change the data (Remember what I said about the state of the rows in a DataTable!)
    c) You write back the changes. (This is done by the Update command)

    The DataAdapter needs the correct commands to do all actions. So the SelectCommand should get a command that selects the data from the database. (So you do not give an Insert command there!).

    If you want to use an DataAdapter: Please study it carefully. Thank you.

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 3:38 PM
    Wednesday, July 24, 2013 10:08 AM
  • Hi,

    You have to add the Parameters to the SqlCommand. You have to divide between local variables and parameters. That are 2 different things. And you have a "," to much behind @passw.

    So you could add the lines:
    command.Parameters.AddWithValue("@uname", uname)
    command.Parameters.AddWithValue("@passw", passw)

    (I simply wrote it out of my brain - so maybe I missspelled something!)

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 5:01 PM
    Wednesday, July 24, 2013 4:30 PM
  • Hi,

    not sure, but could it be that you have multiple database files? One inside the project itself and one inside the release / debug folders?

    So maybe you are simply checking the wrong database? Could you tell us more details what you are doing exactly? How do you edit the database? How do you check if the data was inserted?

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 6:03 PM
    Wednesday, July 24, 2013 5:26 PM

All replies

  • adapter.fill is used to fetch records from the database to dataset.

    use command.executescalar() -- this is used for executing dml(insert,update,delete etc..) statements against the datasource.

    Wednesday, July 24, 2013 7:22 AM
  • It now jumps over the codes below, I have now replaced "adaptor.Fill(dataset, "0")" with "command.executescalar()" but it now jumps over the code below it form:

     Dim count = dataset.Tables(0).Rows.Count
                If count > 0 Then
                    MsgBox(txtNusername.Text & "Added System Users", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information, "Add New User")
                   End If

    to the catch statement.


    ga

    Wednesday, July 24, 2013 7:46 AM
  • DONT USE ADAPTER OR DATASET ,it is unnecessary. pls check the database whether the record has been updated or not.
    • Marked as answer by Ageorgia Wednesday, July 24, 2013 3:38 PM
    Wednesday, July 24, 2013 7:56 AM
  • Hi,

    I would recommend to read the ADO.Net introduction on MSDN Library. You mixed up stuff completly!

    1) As Sridhar pointed out: There is no need to use a DataAdapter / DataTable to insert a record. Simply use the SqlConnection and SqlCommand to execute the SQl Statement.
    - Create the Connection
    - Create the Command
    - Execute the Command
    - Dispose Command and Connection (Please check the using statement! That is something that makes it straight forward and it is really easy to use!)

    2) You can of course use a DataTable / DataAdapter. But you have to understand that (And the MSDN Library describes it in detail!). In short the technology is used in this way:
    - DataSet / DataTable is an Offline copy of existing data. The great thing is, that it is not just storing the data but also the state (e.g. modified, added, deleted) and you can even access "old" values e.g. froma modified row you can read the original data.
    - The DataAdapter is simply a pattern that should help you to read / write data. So when you use the DataAdapter, you are doing this:
    a) You get an offline copy of the data. (The Fill method is used for this)
    b) You change the data (Remember what I said about the state of the rows in a DataTable!)
    c) You write back the changes. (This is done by the Update command)

    The DataAdapter needs the correct commands to do all actions. So the SelectCommand should get a command that selects the data from the database. (So you do not give an Insert command there!).

    If you want to use an DataAdapter: Please study it carefully. Thank you.

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 3:38 PM
    Wednesday, July 24, 2013 10:08 AM
  • Thank you very much for your support. I really appreciate.

    I tried a new set of code shown below

    Dim connection As New SqlConnection
            Dim command As New SqlCommand
            Try
                connection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataBase.mdf;Integrated Security=True;User Instance=True"
                connection.Open()
                command.Connection = connection
                Dim uname, passw As String
                uname = txtNusername.Text
                passw = txtNpassword.Text
                command.CommandText = "INSERT INTO Users([UserName], [Passwords]) VALUES(@uname, @passw,)"
                command.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
            Finally
                connection.Close()
            End Try

    After debugging the code I get the error message -- Must declare the scalar variable "@uname"

    The database is not updated. Please need your help.

    Thank you


    ga

    Wednesday, July 24, 2013 3:48 PM
  • Hi,

    You have to add the Parameters to the SqlCommand. You have to divide between local variables and parameters. That are 2 different things. And you have a "," to much behind @passw.

    So you could add the lines:
    command.Parameters.AddWithValue("@uname", uname)
    command.Parameters.AddWithValue("@passw", passw)

    (I simply wrote it out of my brain - so maybe I missspelled something!)

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 5:01 PM
    Wednesday, July 24, 2013 4:30 PM
  • It is working now but I do not see the new data in the database, any help?

    Thank you Konrad


    ga

    Wednesday, July 24, 2013 5:10 PM
  • Hi,

    not sure, but could it be that you have multiple database files? One inside the project itself and one inside the release / debug folders?

    So maybe you are simply checking the wrong database? Could you tell us more details what you are doing exactly? How do you edit the database? How do you check if the data was inserted?

    With kind regards,

    Konrad

    • Marked as answer by Ageorgia Wednesday, July 24, 2013 6:03 PM
    Wednesday, July 24, 2013 5:26 PM
  • I have one database and for now I have only developed the table for users. I click on the database explorer, right click on the table users and select show table data, this is how I check for the updated data.

    Thank you for your help.



    ga

    Wednesday, July 24, 2013 6:07 PM