why is my "insert" not inserting!!

[gilly]

Im trying to insert a simple line of text into fields in my table.

How come the data in my text file is not inserting into my table. VB brings up no errors & looks like it has worked but when i go into my table there is nothing there. Is there any way i can manually hard code data to see it work?heres my code..

'Open the text file
Set ts = fso.OpenTextFile("c:\windows\desktop\10010253136471.txt")


'Open the table
rst.Open "SELECT * FROM tbl_headerrecord", cn, adOpenDynamic, adLockOptimistic

'If RecordCount = 0 Then
If rst.EOF Then

strsql = "INSERT INTO tbl_headerrecord (recordid,acno)" & _
"," & "VALUES (" & str_recordid & "," & int_acno & ")"

Else

strsql = "UPDATE tbl_headerrecord SET recordid=" & str_recordid & ",acno=" & int_acno & ")"

cn.Execute strsql

[gilly]

help????????

[Si_the_geek]

erm... where are you setting str_recordid and int_acno??

does the table have anything added (eg: nulls/empty strings) as you have it? if not, are you sure the connection is ok?

[gilly]

I havent SET str_recordid and int_acno....i have only used dim recordid as string and dim acno as integer.

Do i need to set them also?

At present my table is empty, maybe thats the problem?

Yes..connection is fine

??

[Si_the_geek]

if you don't set them then your insert/update statements have no data, so nothing gets put into the table (or nulls, depending on how the database is set up)

You need to set them to something, from what you said in the first post I assume that you want to get the data from the file - after you open it set the variables to whatever you want from the file...


to test the database access you could set these to test values in your code before you run it, eg: str_recordid = "test"

[gilly]

when i added in a "dummy" row of data to my table it went past the original error & stops at cn.execut strsql, saying there is a syntax error in my update statement. Do i still need to set something?

In order to SET the data, what is the syntax to start me off? I have a SET in my update statement. What else do i need?

Thanks for ur time
xxG

[Negative0]

Remove the Close Parentheses at the end of your update statement. And i think your insert was not working because the execute command looks like it is still in the else part of the If statement.

Hope this helps,

[Si_the_geek]

Here's an updated version of your code that should work a bit better....

str_recordid = "test"
int_acno = 1
 
'Open the table 
rst.Open "SELECT * FROM tbl_headerrecord", cn, adOpenDynamic, adLockOptimistic 
 
'If RecordCount = 0 Then 
If rst.EOF Then 
 
 strsql = "INSERT INTO tbl_headerrecord (recordid,acno)" & _ 
"," & "VALUES ('" & str_recordid & "'," & int_acno & ")" 
 
Else 
 
 strsql = "UPDATE tbl_headerrecord SET recordid='" & str_recordid & "',acno=" & int_acno & ")" 
 
end if
cn.Execute strsql

notice the variables being set at the start, and the extra quotes in the sql string around str_recordid (so that the database takes it as a string, rather than as field names - which is why you are getting the new error).