Solved

Help with Access and Visual Basic Insert statement

Posted on 2007-04-01
3
195 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
3 Comments
 
LVL 142

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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Remove the "Set up OneDrive" pop-up 3 129
Unable to Uninstall Visual Studio 2015 7 27
No Data for DropDown List 2 26
How to remove unwanted words? 34 40
A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

832 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