Insert Problem

• • Reply

  14 years ago

  by John D.

  conwayjd

  United States, United States

  Joined 15 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)
  Report abuse
• • Reply

  14 years ago

  by chathu

  Chathura Wijekoon

  , Sri Lanka

  Joined 14 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
  Report abuse
• • Reply

  14 years ago

  by angieb1008

  Angie Buckner

  , United States

  Joined 14 years ago
  I am by no means an expert but I think you need to use Commandbuilder.
  
  
  Report abuse
• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 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.
  
  
  
  
  Report abuse