URGENT - Sql INSERT

HI,

I have this SQL Statement that i would like to do, but i don't think the syntax is correct, could someone help please.

SqlRs = "INSERT INTO Entry_de (Error Code) values ('" & ErrCodeE & "') where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & ""
                                ELITEDB.Execute SqlRs

Basically what i would like to do, is - I would like to insert a value into a certain field in a table, according to certain criteria. The column name is Error Code, and i would like the value ErrCodeE to be inserted into that field. The Entry_de is the table i would like to insert this value. And the criteria, is that i would like the ErrCodeE to be inserted where the Batch Number = x, and Entry Number = y.

Please could someone help.
Thanks!
mscalaAsked:
Who is Participating?
 
TchalkovCommented:
Try:
update <tableName> set Field1=Value1,..,FieledN=valueN where <criteria>
0
 
AzraSoundCommented:
try this:
INSERT INTO Entry_de(ErrorCode)
SELECT Batch#, Entry#
FROM tableName
WHERE Batch# = x AND Entry# = y;
0
 
AzraSoundCommented:
scratch that
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AzraSoundCommented:
are all of these variables coming from the same table?
0
 
mscalaAuthor Commented:
AzraSound

Error Code = Field in Table(Entry_de)
ErrCodeE = Value

I want to put ErrCodeE into Error Code.
Batch Number = Field in Table(Entry_de)
Entry Number = Field in Table(Entry_de)

But this is just the criteria, they have values.
0
 
mscalaAuthor Commented:
Yes, i am dealing only with one table.
0
 
AzraSoundCommented:
yes my statement isnt even the correct syntax...I shouldnt post at 1 AM anymore
0
 
mscalaAuthor Commented:
Yes, i am dealing only with one table.
0
 
AzraSoundCommented:
tchalkov's solution should work, for your case:


UPDATE Entry_de
SET ErrorCode = "ErrCodeE"
WHERE Batch# = x AND Entry# = y;
0
 
mscalaAuthor Commented:
AzaSound

So you can't help me with the syntax of this statement?
0
 
mscalaAuthor Commented:
AzaSound

So you can't help me with the syntax of this statement?
0
 
AshokKumarCommented:
Friend. First insert the entire row leaving the field Erro_Code field as Null. After that open the table and by each record check for the combination of your where condition. If condition satisfies then update error_code.

Code:

Open the recordset for Table Entry_de say Rs

Do while Not Rs.Eof
    If Rs(Batch Number) = x And Rs(Entry Number) = y Then
        Sql = "UpDate Entry_de Set Error_Code = '" & ErrorCode & "'"
        Db.Execute Sql    
    End If
    Exit Do  'Only if u need to update the first record for this matching condition
Loop

This should solve the problem.
0
 
RobjCommented:
It's just as Tchalkov said...

SqlRs = "Update Entry_de Set (Error Code) = ('" & ErrCodeE & "') where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & ""
                                ELITEDB.Execute SqlRs
0
 
mscalaAuthor Commented:
AzaSound

So you can't help me with the syntax of this statement?
0
 
AshokKumarCommented:
NO Robj. mscala wanted this to be executed when the table is inserted for the first time. How can u update a record when there is no data. So, i first inserted it with blank field and then inserted the field value. Ok. Fine. this is the code and what else u need mscala. What is your problem exactly. What do u mean by "So you can't help me with the syntax of this statement?". The entire syntax is given for you.


0
 
wqwCommented:
the problem is with you Error Code column. you must quote it's name. also you could check for existence this way:

SqlRs = "IF Exists(Select * From Entry_de Where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & ")" & vbcrlf & _
"UPDATE Entry_de Set [Error Code]) = '" & ErrCodeE & "' where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & vbcrlf & _
"ELSE INSERT INTO Entry_de ([Error Code]) values ('" & ErrCodeE & "') where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo

ELITEDB.Execute SqlRs

HTH,

</wqw>
0
 
mscalaAuthor Commented:
AzaSound

So you can't help me with the syntax of this statement?
0
 
wqwCommented:
ooops, no WHERE clause needed on insert.

:-)

</wqw>
0
 
mscalaAuthor Commented:
AzaSound

So you can't help me with the syntax of this statement?
0
 
mscalaAuthor Commented:
This is my Sql Statement:

SqlRs = "INSERT INTO Entry_de (Error) values ('" & ErrCodeE & "') where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & ";"
                                ELITEDB.Execute SqlRs
It says there is a missing semi-colon at the end of the statement?
But it has one there.
0
 
wqwCommented:
mscala: Insert statement DOES NOT have where clause. you are totally confusing SQL parser. it understands it as to statements

insert ....
where ...

second one being TOTALLY incorrect

HTH,

</wqw>
0
 
mscalaAuthor Commented:
This is my Sql Statement:

SqlRs = "INSERT INTO Entry_de (Error) values ('" & ErrCodeE & "') where [Batch Number] = " & WBBatchNumber & " And [Entry Number] = " & LineNo & ";"
                                ELITEDB.Execute SqlRs
It says there is a missing semi-colon at the end of the statement?
But it has one there.
0
 
mscalaAuthor Commented:
Tchalkov's answer was correct using update, just has to get the syntax in order, thanks to Robj aswell, and thank you to all of you for your help

Thanks!
0
 
AshokKumarCommented:
mscala. What's wrong with my code. how on earth can u have an where condition in Insert statement. Only in an update statement u can have it. Anyway, if u r satisfied, then it is ok for me. But y u rejected mine.
0
 
mscalaAuthor Commented:
AshokKumar

I am not using the insert statement i am using the update statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.