Problem with insert statement...

[lidds]

I currently have a table call fileStorageTbl which has the following columns:

 

Revision varchar(2)

FileType varchar(50)

Version varchar(4)

Title varchar(255)

User varchar(50)

Name varchar(100)

Date varchar(20)

Time varchar(20)

Picture image(16)

 

but when I use the following statement it does not work:

Dim strRevision As String
       Dim strFileType As String
       Dim strVersion As String
       Dim strTitle As String
       Dim strUser As String
       Dim strName As String
       Dim strTime As String
       Dim strDate As String
       Dim strAlias As String

       strRevision = Me.comboRev.SelectedItem
       strFileType = Me.comboFileType.SelectedItem
       strVersion = Me.comboVersion.SelectedItem
       strTitle = Me.txtTitle.Text
       strName = "simon liddicott"
       strTime = "13:43:10"
       strDate = "02/02/2005"
       strAlias = "lidds"

       Dim myConn As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=(local);initial catalog=testdb;user id=admin;password=simon")
       Dim myInsertCmd As New OleDb.OleDbCommand("INSERT INTO FileStorageTbl (Revision,FileType,Version,Title,User,Name,Date,Time) VALUES ('" & strRevision & "','" & strFileType & "','" & strVersion & "','" & strTitle & "','" & strAlias & "','" & strName & "','" & strDate & "','" & strTime & "')", myConn)

       myConn.Open()
       myInsertCmd.ExecuteNonQuery()
       myConn.Close()

 

Can anyone help?

 

Simon

[HJB417]

get in the habit of using parameters

[Nerseus]

What's the error you get?

What are some of the values you use?

 

Try changing the line that sets myInsertCmd to first build the string into its own variable, then passing that. That way, you can examine the string before you make the call. For example:

 

Dim sql As String = "INSERT INTO FileStorageTbl (Revision,FileType,Version,Title,User,Name,Date,Time) VALUES ('" & strRevision & "','" & strFileType & "','" & strVersion & "','" & strTitle & "','" & strAlias & "','" & strName & "','" & strDate & "','" & strTime & "')"

Dim myInsertCmd As New OleDb.OleDbCommand(sql, myConn)

 

-ner

[lidds]

The error I got was:

 

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

I tried what you suggested but it still gave me the same error, I am really stumped on this as I have done insert statements numerous amount of times. Anyway these are the values I am using.

 

strRevision = "0"

strFileType = "Isometric"

strVersion = "1.0"

strTitle = "test upload"

strName = "simon liddicott"

strTime = "13:43:10"

strDate = "02/02/2005"

strAlias = "lidds"

 

Any ideas

 

Simon

[PlausiblyDamp]

Try enclosing the dates inside # rather than single quotes. Or alternatively try using a parameterised query.

[Optikal]

Set a breakpoint and grab the value of CommandText just before you execute it, then copy-paste that into Query Analyzer (or Access, or whatever DB you happen to be using). Then you will get a more descriptive error why it fails.