sql insert syntax 3

[joeschoe]

I want to create a table and add data to it.
I am coding in VB6 and the database engine is MSDE.
I created the table with one field, no indexes.
I then tried to insert one record, I get the following error message

Run-time error ‘-214721833(80040e57)’:
String or binary data would be truncated.

Could someone please point out what I am doing wrong.

The routine where I create the database is the following:

Public Sub ADOXCreateTable(Tab_name As String)
Dim tbL As New ADOX.Table
Set catDB.ActiveConnection = connDB 
‘connDB is set in form load and defined as public object
On Error Resume Next
Set tbL = catDB.Tables(Tab_name)
If Err.Number > 0 Then
        Debug.Print Tab_name & " doesn't yet exist " & Err.Description
        With tbL
           .Name = Tab_name
           Select Case .Name
             Case "CitiesIndex"
                .Columns.Append "CityName", adVarWChar
                On Error Resume Next
                catDB.Tables.Append tbL
                If Err.Number <> 0 Then
                 Debug.Print .Name & " exists " & Err.Description
                Else
                 Debug.Print .Name & " created"
                End If
             End Select
        End With
       Else
        Debug.Print Tab_name & " exists"
        
        Set tbL = Nothing
    End If
    Set tbL = Nothing
    Exit Sub

I then try to add data into the table :

Dim tbL As New ADOX.Table
Dim rs As New ADODB.Recordset
Dim record_txt, i, txt
Set catDB = New ADOX.Catalog
Set connDB = New ADODB.Connection
connDB.CursorLocation = adUseClient
connDB.ConnectionString = "Provider=SQLOLEDB;" & _
    "uid=sa;pwd=1234;" & _
    "Data Source='JOESNBOOK\CITYTRACKER';" & _
    "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
connDB.Open
Set rs = connDB.Execute("SELECT * FROM " & "CitiesIndex", , adCmdText)
Do Until rs.EOF
Debug.Print rs.Fields(0).Value
Loop
Dim qstr As String
qstr = "INSERT INTO CitiesIndex (CityName) VALUES ('NEWYORK')"
Set rs = connDB.Execute(qstr)
rs.Close

I keep hanging on the INSERT command.

 

[PatrickIRL]

Don't you have to still give the size of the field??

.Columns.Append "CityName", adVarWChar (20)

Patrick

"It works in practice, now we have to figure out if it works in theory?

 

[Golom]

Your code seems to have some problems

connDB.Open
Set rs = connDB.Execute("SELECT * FROM " & "CitiesIndex", , adCmdText)
[COLOR=red] This loop has no MoveNext. It will never terminate if rs.EOF is false[/color]
Do Until rs.EOF
Debug.Print rs.Fields(0).Value
Loop
Dim qstr As String
qstr = "INSERT INTO CitiesIndex (CityName) VALUES ('NEWYORK')"
[COLOR=red]qstr is an SQL Action query that returns no records. There's nothing to assign to a recordset.

rs is an open recordset at this point. Attempting to create a new instance without closing the existing one will raise an error.[/color]
Set rs = connDB.Execute(qstr)
rs.Close

I suggest

Dim qstr            As String
Dim RecordsAffected As Long

Set connDB = New ADODB.Connection
connDB.CursorLocation = adUseClient
connDB.ConnectionString = "Provider=SQLOLEDB;" & _
    "uid=sa;pwd=1234;" & _
    "Data Source='JOESNBOOK\CITYTRACKER';" & _
    "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
connDB.Open
qstr = "INSERT INTO CitiesIndex (CityName) VALUES ('NEWYORK')"
connDB.Execute qstr, RecordsAffected

 

[BobRodes]

<Don't you have to still give the size of the field??
Yeah, I believe when you don't put the number of characters in the field, it assumes a value of 1.

Varchar does NOT mean that the field has any number of characters. It means that it has any number of characters up to the limit specified. The difference between char and varchar is simply that char will pad a value with trailing spaces up to the specified size and varchar won't.

HTH

Bob