ADO.Net 1.1 : SQL Insert ?

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.

Mark
Thu May 26 01:12:21 CDT 2005

"sam" <samuellai@ajikl.com.my> wrote in message
news:e5pY1daYFHA.3864@TK2MSFTNGP10.phx.gbl...

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

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?

>
>
> 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.
>

Sahil
Thu May 26 03:47:11 CDT 2005

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/


"Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:%23GvVjnbYFHA.3572@TK2MSFTNGP12.phx.gbl...
> "sam" <samuellai@ajikl.com.my> wrote in message
> news:e5pY1daYFHA.3864@TK2MSFTNGP10.phx.gbl...
>
>> How do I convert this ASP 3.0 to VB.Net coding?
>
> 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?
>
>>
>>
>> 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.
>>
>
>

Cor
Thu May 26 06:35:36 CDT 2005

Sam,

Maybe can you find something in this walkthrough.
http://support.microsoft.com/default.aspx?scid=kb;en-us;308485

I hope this helps,

Cor

sam
Sun May 29 21:35:51 CDT 2005

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

"Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:%23GvVjnbYFHA.3572@TK2MSFTNGP12.phx.gbl...
> "sam" <samuellai@ajikl.com.my> wrote in message
> news:e5pY1daYFHA.3864@TK2MSFTNGP10.phx.gbl...
>
>> How do I convert this ASP 3.0 to VB.Net coding?
>
> 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?
>
>>
>>
>> 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.
>>
>
>