Insert Problem

  • 11 years ago

    The changes are not showing up in the DB

    When I input data in the textbox(s) and press the btnInsert i get the message that the info is saved to the DB but when I close an reopen the  form the changes are not sent to the database.

    What am I doing wrong? 

    Dim myConnection As New SqlConnection

    myConnection =

    New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\HomeInventory.mdf;Integrated Security=True; Connect Timeout=30;User Instance=True;")

    Dim myAdapter As New SqlDataAdapter

    Dim myCommand As New SqlCommand

    myCommand =

    New SqlCommand("Select * from Stores", myConnection)

    myCommand.Parameters.Add(

    "@storeid", SqlDbType.Int, 50)

    myCommand.Parameters.Add(

    "@storename", SqlDbType.NVarChar, 50)

    myCommand.Parameters.Add(

    "@Address", SqlDbType.NVarChar, 50)

    myCommand.Parameters.Add(

    "@City", SqlDbType.NVarChar, 50)

    myCommand.Parameters.Add(

    "@State", SqlDbType.NVarChar, 50)

    myCommand.Parameters.Add(

    "@Zip", SqlDbType.NVarChar, 50)

    myCommand.Parameters.Add(

    "@phone", SqlDbType.NVarChar, 50)

    myAdapter.SelectCommand = myCommand

    myCommand =

    New SqlCommand("INSERT INTO Stores(StoreID,StoreName,Address, City,State, Zip,Phone)Values(@storeid,@storename,@address,@city,@state,@zip,@phone)", myConnection)

    myCommand.Parameters.Add(

    "@storeid", SqlDbType.Int, 50, "StoreID")

    myCommand.Parameters.Add(

    "@storename", SqlDbType.NVarChar, 50, "StoreName")

    myCommand.Parameters.Add(

    "@Address", SqlDbType.NVarChar, 50, "Address")

    myCommand.Parameters.Add(

    "@City", SqlDbType.NVarChar, 50, "City")

    myCommand.Parameters.Add(

    "@State", SqlDbType.NVarChar, 50, "State")

    myCommand.Parameters.Add(

    "@Zip", SqlDbType.NVarChar, 50, "Zip")

    myCommand.Parameters.Add(

    "@phone", SqlDbType.NVarChar, 50, "Phone")

    myCommand.Parameters(

    "@storeID").Value = StoreIDTextBox.Text

    myCommand.Parameters(

    "@storename").Value = StoreNameTextBox.Text

    myCommand.Parameters(

    "@address").Value = AddressTextBox.Text

    myCommand.Parameters(

    "@City").Value = CityTextBox.Text

    myCommand.Parameters(

    "@State").Value = StateTextBox.Text

    myCommand.Parameters(

    "@Zip").Value = ZipTextBox.Text

    myCommand.Parameters(

    "@phone").Value = PhoneTextBox.Text

    myAdapter.InsertCommand = myCommand

     

    Me.StoresTableAdapter.Update(Me.HomeInventoryDataSet.Stores)

     

    MsgBox(

    "Data Was SuccessFully Added To The DataBase", MsgBoxStyle.Information)
  • 11 years ago
    Why didn't you use an exception. I can't find anything more than that. Please replace your last two lines with
    =======
    Try
    Me.StoresTableAdapter.Update(Me.HomeInventoryDataSet.Stores)
    MsgBox( "Data Was SuccessFully Added To The DataBase", MsgBoxStyle.Information)

    Catch e As Exception
    MsgBox( "Data Was not Added To The DataBase", MsgBoxStyle.Exclamation)

    Finally
    if not myConnection is nothing then
    myConnection=nothing
    endif
  • 10 years ago
    I am by no means an expert but I think you need to use Commandbuilder.

  • 10 years ago
    Hi,

    I dont know why you use or need a select statement before the insert, you didn't open the connection and you didn't execute the command.

    This method you use:
    Me.StoresTableAdapter.Update(Me.HomeInventoryDataSet.Stores)
    
    Works if you are using a dataset and in your code I don’t see any instance of Me.HomeInventoryDataSet.Stores in the insert command. You have to choose in which way you want to work if is with dataset or dataproviders commands.
    Dim myConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\HomeInventory.mdf;Integrated Security=True; Connect Timeout=30;User Instance=True;")
    myConnection.Open()
    
    Try
        Dim myCommand As New SqlCommand("INSERT INTO Stores(StoreID,StoreName,Address, City,State, Zip,Phone)Values(@storeid,@storename,@address,@city,@state,@zip,@phone)", myConnection)
    
        myCommand.Parameters.Add("@storeid", SqlDbType.Int, 50, "StoreID")
        myCommand.Parameters.Add("@storename", SqlDbType.NVarChar, 50, "StoreName")
        myCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 50, "Address")
        myCommand.Parameters.Add("@City", SqlDbType.NVarChar, 50, "City")
        myCommand.Parameters.Add("@State", SqlDbType.NVarChar, 50, "State")
        myCommand.Parameters.Add("@Zip", SqlDbType.NVarChar, 50, "Zip")
        myCommand.Parameters.Add("@phone", SqlDbType.NVarChar, 50, "Phone")
        myCommand.Parameters("@storeID").Value = StoreIDTextBox.Text
        myCommand.Parameters("@storename").Value = StoreNameTextBox.Text
        myCommand.Parameters("@address").Value = AddressTextBox.Text
        myCommand.Parameters("@City").Value = CityTextBox.Text
        myCommand.Parameters("@State").Value = StateTextBox.Text
        myCommand.Parameters("@Zip").Value = ZipTextBox.Text
        myCommand.Parameters("@phone").Value = PhoneTextBox.Text
    
        myCommand.ExecuteNonQuery()
    
        MsgBox("Data Was SuccessFully Added To The DataBase", MsgBoxStyle.Information)
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
    Finally
        myConnection.Close()
    End Try
    
    Good luck.



Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are 10 types of people in the world, those who can read binary, and those who can't.”