Write data in VB.NET to Access using SQL

Write data in VB.NET to Access using SQL

• gudel

  what problems
  
  

• vppsit

  Aight.  I have used the above information to write my save function BUT nothing is happening.

  Here is some of my code.

   <code>

  Private Sub usersaveBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles usersaveBTN.Click

  Dim ds_user As New usersDataSet1

  Dim ta_user As New usersDataSetTableAdapters.userTableAdapter

  Dim dr_user As usersDataSet.userRow

  Dim ds_gen_info As New usersDataSet1

  Dim ta_gen_info As New usersDataSet1TableAdapters.general_infoTableAdapter

  Dim dr_gen_info As usersDataSet1.general_infoRow

  Dim ds_forgotpswd As New usersDataSet1

  Dim ta_forgotpswd As New usersDataSet1TableAdapters.forgotpwdTableAdapter

  Dim dr_forgotpswd As usersDataSet1.forgotpwdRow

  Dim validate As Integer = 0

   

  dr_user = ds_user.user.NewuserRow

  dr_gen_info = ds_gen_info.general_info.Newgeneral_infoRow

  dr_forgotpswd = ds_forgotpswd.forgotpwd.NewforgotpwdRow

  If fnameTXBX.Text = "" Or lnameTXBX.Text = "" Or addressTXBX.Text = "" Or homephTXBX.Text = "" Or emailTXBX.Text = "" Or usernameTXBX.Text = "" Or passwordTXBX.Text = "" Or securityqcmbx.Text = "" Or secanswerTXBX.Text = "" Then

  MessageBox.Show("You must enter information into all required fields.", "Required Information", MessageBoxButtons.OK)

  Else

  dr_user.user_fname = fnameTXBX.Text

  dr_user.user_lname = lnameTXBX.Text

  dr_gen_info.user_address = addressTXBX.Text

  dr_gen_info.user_city = cityTXBX.Text

  dr_gen_info.user_state = stateTXBX.Text

  dr_gen_info.user_zip = zipTXBX.Text

  dr_gen_info.home_phone = homephTXBX.Text

  dr_gen_info.work_phone = workphTXBX.Text

  dr_gen_info.alt_phone = altphTXBX.Text

  dr_gen_info.email_address = emailTXBX.Text

  dr_user.user_login_name = usernameTXBX.Text

  dr_gen_info.user_login_name = usernameTXBX.Text

  dr_forgotpswd.user_login_name = usernameTXBX.Text

  dr_user.user_login_password = passwordTXBX.Text

  dr_forgotpswd.forgotpwd_question = securityqcmbx.Text

  dr_forgotpswd.forgotpwd_answer = secanswerTXBX.Text

  validate += 1

  End If

  If validate = 1 Then

  MessageBox.Show("User has been added", "Successful", MessageBoxButtons.OK)

  End If

  End Sub

  </code>

  Also here is some more pics of my prog.  http://redbeardcustoms.no-ip.info/VB/adduserform.html

  The code highlighted with the yellow background is where im having a problem i think.  VB.NET has a blue squiggley line underneath it.

   

  ALSO.....

     My entire project is kept on a flash drive b/c i have to take it between home, school, and work.  But each place assigns a different letter drive to it.  How can i go around this

  
  

• enric vives

  please convert the image to a jpeg to reduce the size as it takes a while for it to load a bitmap image (raw)

  yes this can be done - to take values from each control, and insert into the database. The inserting portion sample has been given above, all you need to do is modify it and follow the path/outline/body of the example and implement the correct values/parameters/statement fields and values etc.....

  you simply take those values on the tab control(s) and assign it to a parameter, as I have done, and execute the command. Is this what you are after

  
  
  

• becklighter

  you don't need to worry about the drive letter for the application....
  
  

• ejamashu

  http://redbeardcustoms.no-ip.info/VB/adduserform.html

  
  

• softwarejaeger

  While there's nothing wrong with the route you've been given, for someone who doesn't yet understand the ins and outs of SQL syntax, you may find it much easier to do this with a dataset and let Visual Studio do a lot of work for you.

  I can understand your desire to prove a nay-sayer wrong (I don't like to be told what I am and am not capable of either!).

  What I would suggest is that you add a new DataSet to the project. Add a TableAdapter to the DataSet from the toolbox. This will start a wizard. Click New Connection, change the Data Source to Microsoft Access Database File, and browse to the file. Set a username and password if any and click Test Connection to verify everything is right. Then click ok and next. If propmted to save the connection string, choose yes. Select Use SQL Statements, click next and then configure your query using the statement: SELECT * FROM tablename where tablename is the name of the table in the Access database. Now just keep clicking Next until you get to Finish. The designer will then add the DataTable to the DataSet with the configured TableAdapter. Build the project.

  Now go back to your existing form's Save button code. In the code for the save button, do the following:

  Create an instance of the dataset - Dim ds As New DataSet1

  Create an instance of the tableadapter - Dim ta As New DataSet1TableAdapters.TableNameTableAdapter

  Create an instance of a table row - Dim dr as DataSet1.TableNameRow

  Get a reference to a new row in the table - dr = ds.TableName.NewTableNameRow

  Set each field in the row based on the values in your textboxes - dr.FieldName = TextBoxName.Text
  (repeat this step for each field you need to set)

  Add the new row to the table - ds.TableName.Rows.Add(dr)

  Update the database using the tableadapter - ta.Update(ds.TableName.GetChanges)

  Stick your tongue out as your prof sees the new record added to the access table.

  Now, that's a pretty generic example procedure but it's more or less the steps needed to use a DataSet and update the access database. If you need more help, post the code you get stuck on and any errors and we'll help ya from there!

  Good luck!

  
  
  

• pinkybaby

  well the link doesn't work.... lol

  so let me get this straight, you want to write data to a database correct Which database MS Access or SQL

  either way, there are a good few examples and snippets on these forums - you may wish to do a quick search but of course, we are more than happy to post code for your needs if you can tell us if its actually SQL you are using (just to add, which version ) or MS Access

  
  
  

• Enix591

  Yes, I was wondering if there was any way that I could post a picture of my screens without having to link to another site.

  
  

• Paul Deen

  you can insert data and read data into a database without any problems. Is this what you are pretty much ultimately after You can use Access or SQL to do this job. code snippets can be posted without any problems :-)
  
  

• yonderstar

  ahmedilyas wrote:

  Dim theOleDbConnection as new OleDbConnection("ConnectionStringHere") Dim theOleDbCommand as new OleDbCommand("INSERT INTO [tableName] (field1, field2) VALUES (@p1, @p2)") theOleDbCommand.Connection = theOleDbConnection Dim theParameter as new OleDbParameter("@p1", OleDbType.VarChar, Length) theParameter.Value = "ValueHere" Dim theParameter2 as new OleDbParameter("@p2", OleDbType.VarChar, Length) theParameter2.Value = "ValueHere" theOleDbCommand.Parameters.Add(theParameter) theOleDbCommand.Parameters.Add(theParameter2) theOleDbCommand.Connection.Open() theOleDbCommand.ExecuteNonQuery() theOleDbCommand.Connection.Close()

  Both Dim theParameter as new OleDbParameter("@p1", OleDbType.VarChar, Length) theParameter.Value = "ValueHere" and Dim theParameter2 as new OleDbParameter("@p2", OleDbType.VarChar, Length) theParameter2.Value = "ValueHere" are giving me problems.

  Im using VB.NET 2005

  
  

  
  

• rviswa

  Okay, sorry bout the link. My server went down last night and I haven't had time to bring it back up.

  Now for the database, Im currently writing it in MS Access but I want to eventually convert it over to SQL(MySQL) if I have time.

  My professor said what I want to do is not possible with MS Access but I know it is and he said i didn't have enough experience with VB to write something to use SQL. i want to prove him wrong.

  I would greatly appreciate any help.

  Thanks,

  QWERTYtech

  
  

• Butterflyangel02

  Ok a couple things here...

  First, it wasn't made clear that you had more than one table for your user data. I wouldn't have expected that as each item of user data appears to be a 1 to 1 ratio.

  You don't need three dataset instances. Your declarations could simply be:

  Dim ds_user As New usersDataSet1

  Dim ta_user As New usersDataSetTableAdapters.userTableAdapter

  Dim dr_user As usersDataSet.userRow

  Dim ta_gen_info As New usersDataSet1TableAdapters.general_infoTableAdapter

  Dim dr_gen_info As usersDataSet1.general_infoRow

  Dim ta_forgotpswd As New usersDataSet1TableAdapters.forgotpwdTableAdapter

  Dim dr_forgotpswd As usersDataSet1.forgotpwdRow

  It doesn't look like you ever used those other two DataSet instances anyway...

  When you mouse over the blue squiggly, what does it say It looks like you've either got a typo in your post or you created more than one dataset...

  Dim ds_user As New usersDataSet1
  Dim dr_user As usersDataSet.userRow
  

  Shouldn't that be: dr_user As usersDataSet1.userRow

  Finally, you're not adding the new rows to the tables or calling Update() on each TableAdapter to actually save your changes back to the database (you missed those last steps).

  After the rows have values set, you need to add each one to its table:

  ds_user.user.Rows.Add(dr_user)
  ds_user.general_info.Rows.Add(dr_gen_info)
  ds_user.forgotpwd.Rows.Add(dr_forgotpswd)

  Then call update on each table adapter to save the changes back to the database:

  ta_user.Update(ds_user.user.GetChanges)
  ta_gen_info.Update(ds_user.general_info.GetChanges)
  ta_forgotpswd.Update(ds_user.forgotpwd.GetChanges)

  Try all that and see how it goes.

  
  
  

• Dhondtie

  I dont think these forums yet have a feature to attach a picture but only links to a picture.

  As for inserting into database, this is not a problem. Here is a small snippet of code which will insert data into an MS Access database. Import the System.Data.OleDb namespace to make this work

  Now, the code supplied is just a snippet. You need to adjust the fields to insert into, the table name, the values, datatypes etc.... in order to make it work. The fieldnames and datatypes must match from the database into the code below as otherwise you would get an exception thrown stating that the datatypes are not correct or can't insert into a column

  Dim theOleDbConnection as new OleDbConnection("ConnectionStringHere") Dim theOleDbCommand as new OleDbCommand("INSERT INTO [tableName] (field1, field2) VALUES (@p1, @p2)") theOleDbCommand.Connection = theOleDbConnection Dim theParameter as new OleDbParameter("@p1", OleDbType.VarChar, Length) theParameter.Value = "ValueHere" Dim theParameter2 as new OleDbParameter("@p2", OleDbType.VarChar, Length) theParameter2.Value = "ValueHere" theOleDbCommand.Parameters.Add(theParameter) theOleDbCommand.Parameters.Add(theParameter2) theOleDbCommand.Connection.Open() theOleDbCommand.ExecuteNonQuery() theOleDbCommand.Connection.Close()

  The code will:

• create a connection to the database

• create an OleDbCommand - the command used to execute your queries, in this case an insert statement

• create parameters (safer than direct values insertion) and add values to those parameters. These values being the values you want to insert into the database field

• add parameters into the OleDbCommand parameter collection

• opens the connection to the database

• executes the non query

• closes the connection to the database

  The same thing applies for SQL, pretty much the same except generally the SQL classes are used (System.Data.SqlClient)

  Does this help you get up and running