Solved

Help with Access and Visual Basic Insert statement

Posted on 2007-04-01
3
199 Views
Last Modified: 2010-04-23
I am trying to insert data into a access database using textboxes and check boxes  from a visual basic written form. Here is my query. I keep getting errors / not inserting at all into the database. Can someone help me. ps all the textbox names are correct.


Dim sqlInventoryString As String = "INSERT INTO Inventory (ID, First Name, Last Name, Description, Order Placed, Order Shipped, Order Received, Comments)  values("",txtfirstname.text, txtlastname.text, txtdescription.text,checkboxorderplaced.checked, checkboxordershipped.checked, checkboxorderreceived.check, richtxtcomments.text);"

        Dim sqlCommand As New OleDb.OleDbCommand(sqlInventoryString, logInOleDbConnection)
        ' Open the database connection.

        logInOleDbConnection.Open()

        ' Run the sqlCommand.
        Dim countFound As Integer = CType(sqlCommand.ExecuteScalar, Integer)

        ' Close the database connection.

        logInOleDbConnection.Close()

        ' If countFound = 1 a record with a matching UserName and Password
        ' was found..

        If countFound = 1 Then

        Else
            Label13.Text = "Error Found Please Retry"
0
Comment
Question by:jejernig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 18832589
Hello jejernig,

  your code does not put the value of the textbox into the sql string, but the name itself
  the following will work better:

Dim sqlInventoryString As String = "INSERT INTO Inventory (ID, First Name, Last Name, Description, Order Placed, Order Shipped, Order Received, Comments)  values('', '" & txtfirstname.text & "','" 1  txtlastname.text & "','" & txtdescription.text & "', " & checkboxorderplaced.checked & "," & checkboxordershipped.checked & "," & checkboxorderreceived.check & ",'" & richtxtcomments.text & "');"

  note however that you should learn about the SqlCommand/OledbCommand with Parameters exection of sql, that will
  1) work faster
  2) work more secure
  3) avoid unecessary code
 

Regards,

angelIII
0
 

Author Comment

by:jejernig
ID: 18832659
Thanks for the swift response. 1 more thing and i will  give you the points.
I am getting a Syntax Error problem at this statement
Dim countFound As Integer = CType(sqlCommand.ExecuteScalar, Integer)

Why is that?
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 125 total points
ID: 18832691
Some of your field names have spaces in them.  You will need to enclose those in [].  E.g

    INSERT INTO Inventory (ID, [First Name], ....

Roger
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An article on effective troubleshooting
There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question