Insert statement not working

neosapien

Member
Joined
Jun 27, 2012
Messages
8
Programming Experience
1-3
Hi there. This may seem to be an age-old problem to many of you, but I have searched almost everywhere, and haven't found a solution that worked. I have an access database on a network drive, which I am viewing on a datagridview on my form. This DB has 5 columns including the primary key, which i an autonumber column named ID. The other 4 are Number(string), Name(String), User(String) and Datetime(Date). I am getting values on the form to be inserted into the DB, but it does not seem to work. When I use this:

cmd = New OleDbCommand("insert into Spareparts(Number, Name, User, Datetime) values ('" & projectListBox.SelectedItem.ToString.Substring(projectListBox.SelectedItem.ToString.Length - 3, 3) & "-" & numberTextbox.Text & "-" & revisionTextbox.Text & "','" & nameTextbox.Text & "','" & userTextbox.Text & "'," & Today.Date & ")", conn)

It tells me 'syntax error in INSERT INTO statement'. But when I exclude the column names:

cmd = New OleDbCommand("insert into Spareparts values ('" & projectListBox.SelectedItem.ToString.Substring(projectListBox.SelectedItem.ToString.Length - 3, 3) & "-" & numberTextbox.Text & "-" & revisionTextbox.Text & "','" & nameTextbox.Text & "','" & userTextbox.Text & "'," & Today.Date & ")", conn)

It tells me 'Number of query values and destination fields are not the same'.

Although, it DOES work when I specify a value for the ID column. I don't want to do that (obviously, that's why I made that field autonumber). Does anyone know why this is happening?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
Well, what does the actually String look like after you've built it? Have you even looked? Clearly, either what you intend the String to contain is wrong to begin with or else it doesn't contain what you expect it to. In order to determine which, you obviously need to know what the String contains. If you do that and you're still confused, post back and show us what the String contains and then we can tell you what's wrong with it.

Regardless of that, there are some changes that you should be making even if that code does work. Firstly, never use string concatenation to insert values into SQL code. Always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

Also, you want to avoid code like this in any circumstances:
VB.NET:
[B][U]projectListBox.SelectedItem.ToString[/U][/B].Substring([B][U]projectListBox.SelectedItem.ToString[/U][/B].Length - 3, 3)
Using long expressions like that multiple times is always a bad thing, but especially inside a much longer expression like that string concatenation because it becomes very difficult to read. In cases like that, you should start by assigning the expression to a variable and then you can use that variable multiple times in the rest of the code. You don't need such a long expression either. If you want the text of the selected item then you can just use the Text property. Also, if you want a substring all the way to the end of a String then you don't need to specify a start and and end, just a start. So, that code becomes:
VB.NET:
Dim projectText = projectListBox.Text
Dim projectSubstring = projectText.Substring(projectText.Length - 3)
You can then use that 'projectSubstring' variable elsewhere in your code without making it hard to read, either to set a parameter value or something else.
 

neosapien

Member
Joined
Jun 27, 2012
Messages
8
Programming Experience
1-3
Thank you so much for the advice. I'm sorry I confused you with the substring. I should have substituted it. Also, about getting the substring till the last character - that was a stupid mistake as well. :) However, I checked the value of the statement. It seems to be:

insert into Spareparts values ('002-10009-AA','Spring Loader','Tom.Decker',21/4/2013)

Is this wrong?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
Yes it is wrong, for two reasons.

1. If you don't provide a column list for an INSERT statement then you must provide a value for every column in the same order that they appear in the table definition. Your table has an auto-generated PK though. You can't explicitly insert a value into that column so you can't provide a value in your INSERT statement so you can't omit the column list.

2. If you're going to put a literal date in a SQL statement then it needs to be delimited. What you intend to be a date there is not being interpreted as a date. I would have expected it to be interpreted as "21 divided by 4 divided by 2013" but I'm not sure that that's happening either. In Jet SQL, dates should be of the form #M/dd/yyyy#.

The thing is though, if you use parameters then things like delimiters and formats become irrelevant, which is one of the main reasons to use them. I address dates and parameters specifically in the Blog post I directed you to earlier.
 

neosapien

Member
Joined
Jun 27, 2012
Messages
8
Programming Experience
1-3
In response to your first point, weirdly enough, when I try to insert a value into the PK column, it works perfectly. And yes I'm sure it is a PK and autonumber field. Lets say I DO use the column list. How will I input the PK column value - or have it auto-generated?

Second point - the date works perfectly(!) No problems there. It was inserted in the correct format, when I specify a value for the PK column. Should I refer to the link 'My Blog' in your signature? I didn't find a section on ADO.NET. :(
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,348
Location
Sydney, Australia
Programming Experience
10+
You should specify a column list, you should not specify a value for the PK and you should read the Blog post I specified and use parameters as it demonstrates. If you can't find it then you haven't looked properly.
 
Top Bottom