Problem with insert statement...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
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:
Code: 
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
 
Whats 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:

Code: 
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
 
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
 
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.
 
Back
Top