RE: how to insert record in a dataSet into another database table

Toyin,

I don't see where you are using cmd.ExecuteNonQuery to actually execute the
Insert statement.

Also, I think AddWithValue is new with .Net 2.0.

Kerry Moorman


"Toyin" wrote:

hello Kerry,
thx for ur response. i tried the example you sent the only response was
"true" and when i check the database table to see if it has insert the new
row it, i found that no row has been inserted. also my IDE does not have
AddWithValue. here is the sample i use to test.

<WebMethod()> Public Function TransferData() As DataSet

Dim sConn As String = "PROVIDER={MSDASQL.1};DSN=myodbc1;SERVER
=localhost;DATABASE =db1;UID =root;PWD = webserver;port=3306"
Dim sComm As String = "select * from table2 where ID = (select
MAX(ID) AS ID from table2)"


Dim conn As New OdbcConnection(sConn)

'Dim comm As New OdbcCommand(sComm, conn)

Dim ds As New DataSet
Dim da As New OdbcDataAdapter

conn.Open()
'comm.ExecuteNonQuery()

da.SelectCommand = New OdbcCommand(sComm, conn)
da.Fill(ds)
conn.Close()

Dim conn1 As New
OdbcConnection("PROVIDER={MSDASQL.1};DSN=myodbc1;SERVER =localhost;DATABASE
=db2;UID =root;PWD = webserver;port=3306")

Dim cmd As New OdbcCommand
cmd.CommandText = "INSERT INTO table1 (ID,Fname,Lname,Sex,DOB)
VALUES (@ID,@Fname,@Lname,@Sex,@DOB)"
cmd.Parameters.Add("@ID", ds.Tables(0).Rows(0)("ID"))
cmd.Parameters.Add("@Fname", ds.Tables(0).Rows(0)("Fname"))
cmd.Parameters.Add("@Lname", ds.Tables(0).Rows(0)("Lname"))
cmd.Parameters.Add("@Sex", ds.Tables(0).Rows(0)("Sex"))
cmd.Parameters.Add("@DOB", ds.Tables(0).Rows(0)("DOB"))
conn1.Open()
cmd.Connection = conn1
conn1.Close()

End Function


.

Relevant Pages