Inserting a record into a table

[mrmotocross]

My problem is i am only getting the first character into the first field. So if a try to insert "12345" only "1" is put into the field. The field is called "Fixture".

here is my code:

Set InsComm = New ADODB.Command
Set InsConn = New ADODB.Connection
Text100 = Mid(CompareText, 1, 100)
InsSql = "INSERT INTO InventoryDescriptions ( Fixture, Price, Lamp, Ballast, Retrokit, LaborCost ) VALUES (Text100, 0, 0, 0, 0, 0);"
InsConn.Open NesConnString
Set InsComm.ActiveConnection = InsConn
InsComm.CommandText = InsSql

On Error GoTo TheError
InsComm.Execute InsSql, adCmdText

GoTo TheClose

TheError:

msg = "ERROR # " & Str(Err.Number) & "Was Generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
Exit Sub

TheClose:

InsConn.Close

End Sub

thanks, bob

 

[Glasgow]

Perhaps check the database definition for the field. If it is char(1) (SQL Server) then I expect you would get these symptoms. Also the declaration of Text100 and Content of CompareText would be relevant.

 

[gmmastros]

It looks like your insSql is wrong. Based on your code, you should be inserting the value 'Text100' in to the field instead of the value for that variable.

Try this instead

     InsSql = "INSERT INTO InventoryDescriptions ( Fixture, Price, Lamp, Ballast, Retrokit, LaborCost ) VALUES (" & Replace(Text100, "'", "''") & ", 0, 0, 0, 0, 0);"

I added the replace function in for you so that it will properly handle the apostrophe character. Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, sorry. Since this is a text field, you need apostrophes around the data.

InsSql = "INSERT INTO InventoryDescriptions ( Fixture, Price, Lamp, Ballast, Retrokit, LaborCost ) VALUES ('" & Replace(Text100, "'", "''") & "', 0, 0, 0, 0, 0);"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Glasgow]

Yep, reckon George has got it and I am blind.

 

[mrmotocross]

thanks everyone, it was the quotes around the text variable that i ws missing! all the best. bob from long island, ny

 

[tsdragon]

I looked at that code and couldn't see a thing wrong with it! It's easy when you're reading the SQL to forget that it isn't JUST a command, it's a command string literal.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.