Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Access and Visual Basic Insert statement

Posted on 2007-04-01
3
Medium Priority
?
203 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 500 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 500 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Windows 10 Creator Update has just been released and I have it working very well on my laptop. Read below for issues, fixes and ideas.
Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

722 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