insert on a specific row

Hi gurus.

Need your help.

I have a sql server table with a few fields already populated, I have a text file that I need to read , the txt file has a field named Doc, I need to read the line whic I have no problem and find the same doc on the sql table and add something to a field on the same row.

How can I accomplish this is sql.- Is there such a thing as select- where and then insert on the same statement.

Thanks a bunch Gurus.

I have done an INSERT/SELECT but never the other way around.

Why not just run a SELECT, then run a separate INSERT?

Thanks H

Once I do the select * from table where Doc=" varaible"

how do I do the insert in that particular row

Sorry for the stupid question but low on knowledge on sql and new to VB as can tell

Thanks for battling with me

Assumptions: You are using ADO code to connect to your database

In this example adoRS is my recordset object and ADOCn is my connection object. Both have been previously declared as project wide Public or form level Private

Code:

---

Dim sSQL As String
Dim strMyVariable As String
sSQL = SELECT field1, field2, field3 FROM tablename WHERE Doc = '" & variablename & "' "
Set adoRS = New ADODB.Recordset
adoRS.Open sSQL, ADOCn
strMyVariable = adoRS.Fields.Item("field1").Value
adoRS.Close
Set adoRS = Nothing
sSQL = "UPDATE tablename SET field1 = '" & strMyVariable & "' "
sSQL = sSQL & "WHERE Doc = '" & variablename & "' "
ADOCn.Execute sSQL

---

Thanks again H

I am using Sqlconnection

can I do something like this, still does not work

stringcommand1 = "INSERT INTO Table1 (Doc,Number)values ('" & "yes" & "','" & Number & "') select docnum from Table1 WHERE docNum=" & DocNumber & ""


tthis is killing me, thanks again H

That is the kind that I've used; i.e. INSERT/SELECT - that should work. Give it a try and post the results.

Thanks H for battling I know is time consuming for you, I really app.

the error I get is the following

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Nothing else

Is "yes" are variable or a literal?

its hard code

Quote:

---

Originally Posted by GUARO

its hard code

---

That means it is a literal. :)

Then do

Code:

---

values ('yes'," & "','" & Number & "')

---

H

I had it working, the problem was that for some reason the field I was reading from the txt file came already as "xxxxx" the when on the sql statement looked like ""xxxxx"" and it could not find it but once I strip it it inserted the value find BUT in a new line not on the same record

the new value was inserted on a new line and did not filled up the line where the doc number was

can you help me on this

Thanks again

H

I think I have to set value as what I am trying to do is change a value that is already there fro something else? from null to yes and from null to text

what do you think and if that is the case how do I do go doing that

H

Can you fix this sql statement please

stringcommand1 = "UPDATE recon SET (CheckNum,Docpaid)VALUES ('" & checkNumber & "', '" & yes & "') WHERE DocNum=" & DocNumber & ""

Wha tis the backend DB? What is the datatype for the field Docpaid?

Again, "yes" is a hard code literal so it should not be encapsulated in double quotes....for literal hard codes (that are text) use just single quotes.

H

my db is sql 2000 and thanks to you I got the problem solved.

Update does not allow () the have to be field=value,field=value and so on, so that part is already fixed.- thanks a bunch for battling with me all this time, I really appreciate.

This is what I call go the extra mile to help.

Thanks again