none
Write data in VB.NET to Access using SQL

    Question

  • Okay,  I have an Administrator page that is able to create new users.  I have a simple adduserform that has a TabControl added in it with a tab for General Info and User Info.  Those have quite a few texboxes for input and outside of the TabControl are 3 buttons that are Save, Clear, and Exit.  What I want to do is have the Save write the data to my small database that I have for users.  If you want to see an example of my screen you can see it here.... http://redbearcustoms.no-ip.info/VB/adduserform.html
    Wednesday, October 04, 2006 5:42 PM

All replies

  • 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

    Wednesday, October 04, 2006 7:35 PM
    Moderator
  • 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

    Thursday, October 05, 2006 4:57 PM
  • 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 :-)
    Thursday, October 05, 2006 5:03 PM
    Moderator
  • 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.

     

    Thursday, October 05, 2006 5:23 PM
  • 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?

Thursday, October 05, 2006 5:40 PM
Moderator
  • 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?

    Thursday, October 05, 2006 6:13 PM
    Moderator
  •  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 = "ValueHereand Dim theParameter2 as new OleDbParameter("@p2", OleDbType.VarChar, Length) theParameter2.Value = "ValueHere" are giving me problems.

    Im using VB.NET 2005

     


     

    Thursday, October 05, 2006 6:30 PM
  • what problems?
    Thursday, October 05, 2006 6:38 PM
    Moderator
  • 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!

    Thursday, October 05, 2006 6:59 PM
    Moderator
  • 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?

    Friday, October 06, 2006 4:38 AM
  •  

    " 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?"

    IF you are running XP Pro (and I don't know about home....)  you can goto

    Plug your disk in and then:

    START | Programs | Adminstrative Tools | Computer Manager | Disk Manager

    Select your disk, right click - Change drive letter........ and you'll have it.

    Friday, October 06, 2006 6:46 AM
  • you don't need to worry about the drive letter for the application....
    Friday, October 06, 2006 7:10 AM
    Moderator
  • 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.

     

    Friday, October 06, 2006 1:34 PM
    Moderator
  •  QWERTYtech wrote:
    Nothing Happens

    See if this link is relative to your problem....

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=427451&SiteID=1

    Friday, October 06, 2006 2:31 PM
    Moderator
  • It's doubtful that link has anything to do with this problem...  He stated that the database was being created in Access, which would put it outside the project so the chance that its being over written is slim.

    Also, he's not calling AcceptChanges() - the code was posted.

    Its quite apparent that new rows aren't being added to existing datatables and the Update() method is never being called.  That's why "nothing happens".

    Friday, October 06, 2006 2:47 PM
    Moderator
  •  rkimble wrote:
    It's doubtful that link has anything to do with this problem... 

    Although you are *probably* correct, I would think that it would be upto the OP to determine if it has anything to do with his problem....(...bite tongue....delete)

    Friday, October 06, 2006 4:48 PM
    Moderator
  • Oh, it certainly is up to the OP to decide.

    But as time is typically precious, there's no sense in wasting the OP's with irrelevant info - especially an admitted noob who may not understand enough of what they read to say if it is related to their problem or not.

    Not that the post you linked to is useless, by any means.  It is certainly a good post with good info.  It just likely has nothing to do with this problem.

    And please, don't hurt yourself biting your tongue.  If you have something to say, my email addy is readily available.

    Cheers

    Friday, October 06, 2006 5:04 PM
    Moderator
  • Aight I made the changes noted back on page one of my thread.  Now whenever I enter test info , a message box that i set to pop up if any info is empt pops up.  But all of my required fields are filled in.  Hmmmmm......  Okay...

    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 usersDataSet1.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

    Dim validate As Integer = 0

     

    dr_user = ds_user.user.NewuserRow

    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_gen_info.user_fname = fnameTXBX.Text

    dr_gen_info.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

    ds_user.user.Rows.Add(dr_user)

    ds_user.general_info.Rows.Add(dr_gen_info)

    ds_user.forgotpwd.Rows.Add(dr_forgotpswd)

    ta_user.Update(ds_user.user.GetChanges)

    ta_gen_info.Update(ds_user.general_info.GetChanges)

    ta_forgotpswd.Update(ds_user.forgotpwd.GetChanges)

    If validate = 1 Then

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

    End If

     

    End Sub

     

    I have taken pictures of everything and they will be on my site @ http://redbeardcustoms.no-ip.info/VB/help.html if you wouldn't mind looking at them and helping a newbie.

    I greatly appreciate everyones help.

     

    Thanks,

    QWERTYtech

     

     

    Friday, October 06, 2006 9:45 PM
  • be sure to check the LENGTH of the textbox, not if its ""

    always check length for string, not by what it may have. Example:

    if Me.theTextBox.Length > 0 then

       'there is data in the textbox

    end if

    Friday, October 06, 2006 10:20 PM
    Moderator
  •  QWERTYtech wrote:

    dr_user = ds_user.user.NewuserRow

    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

    Hi,

    Ahmedilyas is correct. It is much better to check for the length as opposed to the text context. You should change the if statement to look like this....

    If fnameTXBX.Length = 0 Or lnameTXBX.Length = 0 Or addressTXBX.Length = 0 Or homephTXBX.Length = 0 Or emailTXBX.Length = 0 Or usernameTXBX.Length = 0 Or passwordTXBX.Length = 0 Or securityqcmbx.Length = 0 Or secanswerTXBX.Length = 0 Then

    I'm only adding this reply because Ahmedilyas' sample coding will only act if text is found and you are wanting to act if nothing is in the text.

    Good catch Ahmedilyas!!

    Thank you,

    James

    Friday, October 06, 2006 10:41 PM
  •  Rea Software Engineering wrote:
     QWERTYtech wrote:

    dr_user = ds_user.user.NewuserRow

    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

    Hi,

    Ahmedilyas is correct. It is much better to check for the length as opposed to the text context. You should change the if statement to look like this....

    If fnameTXBX.Length = 0 Or lnameTXBX.Length = 0 Or addressTXBX.Length = 0 Or homephTXBX.Length = 0 Or emailTXBX.Length = 0 Or usernameTXBX.Length = 0 Or passwordTXBX.Length = 0 Or securityqcmbx.Length = 0 Or secanswerTXBX.Length = 0 Then

    I'm only adding this reply because Ahmedilyas' sample coding will only act if text is found and you are wanting to act if nothing is in the text.

    Good catch Ahmedilyas!!

    Thank you,

    James

     

    I don't have a .Length option in VB.NET, should i?

    Monday, October 09, 2006 4:50 AM
  • Hi,

    Actually, the Length property is a property to the Text property. I apologize for the above being incorrect. You would access the 'Length' property like this 'Textbox1.Text.Length'

    So your coding should look like this:

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

    I hope this clears the issue up.

    Thank you,

    James

    Monday, October 09, 2006 8:45 AM
  •  QWERTYtech wrote:

    ds_user.user.Rows.Add(dr_user)

    ds_user.general_info.Rows.Add(dr_gen_info)

    ds_user.forgotpwd.Rows.Add(dr_forgotpswd)

    Whenever I populate my form with information i get an 2 errors.  They are the same errors but for different items. 

    Whenever the program gets to ds_user.general_info.Rows.Add(dr_gen_info), the following error appears ArgumentNullException was unhandled 'row' argument cannot be null.  Parameter name: row

    This shows up for both ......Add(dr_gen_info) and .Add(dr_forgotpswd).

    What am I doing wrong?

    Monday, October 09, 2006 4:21 PM
  • Looks like you missed instantiating those rows...

    Your code:

    Dim dr_gen_info As usersDataSet1.general_infoRow

    Dim ta_forgotpswd As New usersDataSet1TableAdapters.forgotpwdTableAdapter

    Dim dr_forgotpswd As usersDataSet1.forgotpwdRow

    Dim validate As Integer = 0

     

    dr_user = ds_user.user.NewuserRow

    Should look like:

    Dim dr_gen_info As usersDataSet1.general_infoRow

    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_user.general_info.Newgeneral_infoRow
    dr_forgotpswd = ds_users.forgotpwd.NewforgotpwdRow

    Adding those two lines should fix that error.

     

    Monday, October 09, 2006 4:58 PM
    Moderator
  • i wrote these code but its not working

    someone can help me?

    thx

    dataset, dataadapter already declared.

    Dim dr As dscustomers.CustomersRow

    dr = Dscustomers1.Customers.NewCustomersRow

    dr.CustomerID = txtbox.Text

    Dscustomers1.Customers.Rows.Add(dr) '' problem look like in this line..

    SqlDataAdapter1.Update(Dscustomers1.Customers.getchanges)

     

    Monday, October 16, 2006 8:34 AM
  • bburcan,

    You have to explain how the code failed... Error messages and such.  What you posted looks fine.

    Plus you've hijacked this thread.  You should start a new one for your problem.

    Monday, October 16, 2006 2:34 PM
    Moderator
  • i would like to make a search in my database or in my datagrid,

    can you help me?how can i do it?

    for example when i write 'A' in textbox , i want to see all names that begins with A.

    thx.

     

    Wednesday, October 18, 2006 7:01 AM