ADO.Net 1.1 : SQL Insert ?

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

How do I convert this ASP 3.0 to VB.Net coding?


ASP 3.0 Coding
-----------------
Set rs = Server.CreateObject("ADODB.Recordset")
rsstatm = "SELECT * FROM F4111"
rs.Open rsstatm, ConnSQL

rs.movefirst

do while not rs.eof

rsAdd = "INSERT INTO F4111A (ILITM,ILLITM,ILAITM) VALUES"

rsAdd = rsAdd & "( " & rs("ILITM")&","
rsAdd = rsAdd & "'" & rs("ILLITM") &"',"
rsAdd = rsAdd & "'" & rs("ILAITM") &"' )"

ConnSQL.Execute(rsAdd)
Set rsAdd = Nothing

rs.movenext
loop

rs.close
Set rs = Nothing
Set rsstatm = Nothing

Please help.

Many thanks.
 
How do I convert this ASP 3.0 to VB.Net coding?
Click to expand...

You're surely not expecting someone to write your code for you, are you...?

1) Create a stored procedure to fetch the records from F4111

2) Create a parameterised stored procedure to insert new records into F4111A

3) Fetch the records from F4111 into an SqlDataReader

4) Loop through the SqlDataReader and add the records to F4111A using the
ExecuteNonQuery method calling the parameterised stored procedure.

Alternatively, your code below appears to be copying the entire contents of
F4111 into F4111A - why not just write a stored procedure to do the whole
thing in one go?
 
Sorry, but I gotta agree with Mark on this - we'd love to help, but not do
your job for you :-)

Anyway, Mark is right, you have to learn VB.NET and ADO.NET to convert this
code.

See you can't see heaven without dying yourself, so you've gotta learn .NET.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
 
How about this coding which convertion from ASP 3.0 to VB.Net 1.1 by myself?

Dim constrRead As String = "server='sqlsvr'; user id='user';
password='password'; Database='ERP'"
Dim sqlconRead As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection(constrRead)
Dim sqlcmdRead As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("Select * From F4111", sqlconRead)

Try
sqlconRead.Open

Dim dbrRead As System.Data.SqlClient.SqlDataReader =
sqlcmdRead.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Do While dbrRead.Read

Dim constrInsert As String = "server='sqlsvr'; user
id='user'; password='password'; Database='ERP'"
Dim sqlconInsert As System.Data.SqlClient.sqlconnection =
New System.Data.SqlClient.sqlconnection(constrInsert)
Dim sqlcmdInsert As System.Data.SqlClient.SqlCommand = New
SqlCommand()

sqlcmdInsert.CommandText = "INSERT INTO F4111A
(ILITM,ILLITM,ILAITM) VALUES

sqlcmdInsert.CommandText = sqlcmdInsert.CommandText &
"( " & dbrRead("ILITM")&","
sqlcmdInsert.CommandText = sqlcmdInsert.CommandText & "'"
& dbrRead("ILLITM") &"',"
sqlcmdInsert.CommandText = sqlcmdInsert.CommandText & "'"
& dbrRead("ILAITM") &"' )"

sqlcmdInsert.Connection = sqlconInsert
Try
sqlconInsert.Open()
sqlcmdInsert.ExecuteNonQuery()

Finally
sqlconInsert.Close()
sqlconInsert.Dispose()
End Try
constrInsert = Nothing
sqlcmdInsert = Nothing

Loop

sqlcmdRead = Nothing
dbrRead = Nothing
Finally
sqlconRead.Close
sqlconRead.Dispose()
End Try

constrRead = Nothing
sqlconRead = Nothing
 
Back
Top