Solved

URGENT - Sql INSERT

Posted on 2000-03-16
25
229 Views
Last Modified: 2010-05-02
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!
0
Comment
Question by:mscala
  • 12
  • 5
  • 3
  • +3
25 Comments
 
LVL 2

Accepted Solution

by:
Tchalkov earned 50 total points
ID: 2622658
Try:
update <tableName> set Field1=Value1,..,FieledN=valueN where <criteria>
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2622666
try this:
INSERT INTO Entry_de(ErrorCode)
SELECT Batch#, Entry#
FROM tableName
WHERE Batch# = x AND Entry# = y;
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2622669
scratch that
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2622677
are all of these variables coming from the same table?
0
 

Author Comment

by:mscala
ID: 2622686
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
 

Author Comment

by:mscala
ID: 2622689
Yes, i am dealing only with one table.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2622697
yes my statement isnt even the correct syntax...I shouldnt post at 1 AM anymore
0
 

Author Comment

by:mscala
ID: 2622703
Yes, i am dealing only with one table.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2622706
tchalkov's solution should work, for your case:


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

Author Comment

by:mscala
ID: 2622708
AzaSound

So you can't help me with the syntax of this statement?
0
 

Author Comment

by:mscala
ID: 2622715
AzaSound

So you can't help me with the syntax of this statement?
0
 

Expert Comment

by:AshokKumar
ID: 2622726
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:Robj
ID: 2622732
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
 

Author Comment

by:mscala
ID: 2622743
AzaSound

So you can't help me with the syntax of this statement?
0
 

Expert Comment

by:AshokKumar
ID: 2622772
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
 
LVL 4

Expert Comment

by:wqw
ID: 2622793
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
 

Author Comment

by:mscala
ID: 2622803
AzaSound

So you can't help me with the syntax of this statement?
0
 
LVL 4

Expert Comment

by:wqw
ID: 2622810
ooops, no WHERE clause needed on insert.

:-)

</wqw>
0
 

Author Comment

by:mscala
ID: 2622820
AzaSound

So you can't help me with the syntax of this statement?
0
 

Author Comment

by:mscala
ID: 2622859
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
 
LVL 4

Expert Comment

by:wqw
ID: 2622876
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
 

Author Comment

by:mscala
ID: 2622902
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
 

Author Comment

by:mscala
ID: 2622907
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
 

Expert Comment

by:AshokKumar
ID: 2622932
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
 

Author Comment

by:mscala
ID: 2622949
AshokKumar

I am not using the insert statement i am using the update statement.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now