Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

URGENT - Sql INSERT

Posted on 2000-03-16
25
232 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
String manipulation in Visual Basic 7 73
Saving history changes to sub form 4 48
Microsoft Access combo box help 2 49
Zip Folders Using Chilkat Routines 1 58
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

840 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