INSERT INTO fails after CREATE TABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT INTO fails after CREATE TABLE

Author

Topic

iikenze
Starting Member

2 Posts

Posted - 2011-03-23 : 22:06:03

Hi, I hope someone can help me. I have code that creates a table in a SQL Server database and then tries to insert multiple rows of data. It is written in VBA. The CREATE TABLE works just fine and I can see my table in the SQL Server database but the INSERT INTO fails everytime. Interesting enough when I test the INSERT string in the SQL Server Management Console it works fine but not through code. Here is my code:

-------------------------------------------------------------
csqlstr = "CREATE TABLE " & gUploadtable & "( "
For k = 0 To myrst.fields.count - 1
fld = ""
For j = 0 To UBound(fldtypenum)
If CStr(myrst.fields(k).Type) = fldtypenum(j) Then
fld = fldtype(j)
fldsz = Min(255, myrst.fields(k).DefinedSize)
Exit For
End If
Next j
If fld = "" Then
fld = "UNKNOWN"
ElseIf Not IsNumeric(myrst.fields(k)) Then
fld = fld & "(" & fldsz & ")"
End If
csqlstr = csqlstr & Replace(myrst.fields(k).Name, " ", "_") & " " & fld & IIf(k = myrst.fields.count - 1, "", ", ")
If InStr(1, fld, "char") > 0 Then ValChar(k) = True
Next k

csqlstr = csqlstr & ")"

Set ObjCommand = CreateObject("ADODB.Command")
ObjCommand.activeconnection = cnnConnect
ObjCommand.CommandType = 1
ObjCommand.CommandText = csqlstr
ObjCommand.Execute
Set ObjCommand = Nothing

'//// WORKS UP TO HERE ///////////////////////////////////
'/// FAILS FROM HERE DOWN ///////////////////////////////
Set rst = CreateObject("ADODB.Recordset")
Set ObjCommand = CreateObject("ADODB.Command")
ObjCommand.activeconnection = cnnConnect
ObjCommand.CommandType = 1
csqlstr = "INSERT INTO dbo." & gUploadtable & " VALUES ("
Do Until myrst.EOF
For j = 0 To myrst.fields.count - 1
sval = myrst.fields(j).Value
If ValChar(j) Then
csqlstr = csqlstr & "'" & sval & "'" & ","
Else
csqlstr = csqlstr & sval & ","
End If
Next j
myrst.movenext
csqlstr = Mid(csqlstr, 1, Len(csqlstr) - 1) & ")"
ObjCommand.CommandText = csqlstr
Set rst = ObjCommand.Execute
Loop
Set ObjCommand = Nothing

'///////////////////////////////////////////////////////////

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-23 : 22:24:58

An error would be helpful.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

iikenze
Starting Member

2 Posts

Posted - 2011-03-23 : 23:06:37

Got it! The beginning of my INSERT INTO statement should be inside the loop. Thanks for askign for an error. It made me turn off all error handling and throw the right error.
Cheers!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-24 : 06:19:17

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources