Insert Problem
-
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 SqlConnectionmyConnection =
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 SqlCommandmyCommand =
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.TextmyCommand.Parameters(
"@storename").Value = StoreNameTextBox.TextmyCommand.Parameters(
"@address").Value = AddressTextBox.TextmyCommand.Parameters(
"@City").Value = CityTextBox.TextmyCommand.Parameters(
"@State").Value = StateTextBox.TextmyCommand.Parameters(
"@Zip").Value = ZipTextBox.TextmyCommand.Parameters(
"@phone").Value = PhoneTextBox.TextmyAdapter.InsertCommand = myCommand
MsgBox(
"Data Was SuccessFully Added To The DataBase", MsgBoxStyle.Information) -
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 -
I am by no means an expert but I think you need to use Commandbuilder.
-
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
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).