Solved

Help with Access and Visual Basic Insert statement

Posted on 2007-04-01
3
200 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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

This article summaries thoughts and ideas from two years of sustained use. It provides good reasoning to make the jump to Windows 10.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
Suggested Courses

623 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