vb.net, copy records from sql server table..

AddWithValue is not a member of System.Data.Odbc.OdbcParamterCollection..

There are the names space i have used and using 1.1 framework
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Data
Imports Microsoft.VisualBasic  

Does it apply for 2.0 framwork only ? Pls help....

AddWithValue is new in Framework 2.0

Roger

I am using framework 1.1 & I dont have visualstudio too.

How should i solve the issue if i use LordWabbit Code.

In essence, the first code I posted is very similar to that posted by LordWabbit.  The main differences are that it creates two commands rather than using one with two different commandtexts, it creates the commands outside the loop just adding the parameter values within the loop, it uses ? instead of parameter names for the markers in the queries.  Oh ... and it should work with framework 1.1 ;-)

Roger

ok, i will try ur code. Thanks

Hi Roger,

 cmdTest.CommandText = "SELECT COUNT(SerialNo) FROM <TableName> WHERE SerialNo = ?"
  cmdTest.Parameters.Add("@SerialNo",<myDataType>)

Could you explain the aboove code, not really getting the meaning of those syntaxes.

Can i just use the code "  cmdTest.Parameters.Add("@SerialNo",<myDataType>) ??

Do i need to declare the <myDataType>

Pls elucidate the above points

cmdInsert.CommandText = "INSERT INTO <TableName> (SerialNo) VALUES (?)"  

Sorry very new devlpement, may be qn sounds stupid...

(?)  What does it mean ?

If u have time pls explain first part of your code.

Thanks in advance

In 1.1 you use myCommand.Parameters.Add("@SerialNo", eleRow("SerialNo")) instead of myCommand.Parameters.AddWithValue("@SerialNo", eleRow("SerialNo"))
same thing at the end of the day though

Sorry.  What I meant when I put <> round something was that you should insert your own name in there.  In

cmdTest.CommandText = "SELECT COUNT(SerialNo) FROM <TableName> WHERE SerialNo = ?"

you would need to replace <TableName> by sales.  I know that because you have specifically said that "i need to copy all the SerialNo from sales table".  But in

cmdInsert.CommandText = "INSERT INTO <TableName> (SerialNo) VALUES (?)"  

I don't know what the table name is.  I know from the connection string that the database name is DBF, but you don't say what the name of the table in that database is that you want to insert the new records into.

Re

  cmdTest.Parameters.Add("@SerialNo",<myDataType>)

you might get away without declaring the datatype, but it would be better practice to do so.  As the field is called SerialNo I imagine that it would be a numerical one: but some "serial numbers" contain lots of letters, too, in which case it would have to be some sort of string datatype.  That's why I left it blank, with a bit in <> for you to fill in.  If you are not sure about datatypes have a look at this

http://msdn2.microsoft.com/en-us/library/system.data.sqldbtype.aspx

for the cmdTest datatypes - that is an SQLcommand - and this

http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbctype.aspx

for the cmdInsert datatypes - that is an ODBC command.

As to the ?, that is a marker to tell the parser when it has to get a value from a parameter rather than just reading it straight from the commandtext.  With SQL commands, those parameters should have names, beginning with @.  As cmdTest is an SQL command I should actually have put

        cmdTest.CommandText = "SELECT COUNT(SerialNo) FROM <TableName> WHERE SerialNo = @SerialNo"

But with ODBC commands, names are not used.  Those just rely in the order in which the parameters appear.  So ? is used as a marker.

I hope that is sufficient explanation, and will allow you to get it working.  But if not, tell me the name of your table in the dbf database and whether the serialno is purely numeric or has some letters (or spaces, or punctuation) in it, and I will make more specific suggestions.

Roger

I agree with Lord Wabbit that that's an alternative in 1.1 and, at the end of the day, that it's the same thing.

Roger

"An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll

Additional information: System error."

Had an unhandled Exception When compiler reaches the below syntatx

  Dim intCount As Integer = CType(myCommand.ExecuteScalar, Integer)

Here is the complete code
====================
 Dim oOBCConnection As OdbcConnection
        Dim sConnectionString As String = "Driver={Microsoft Visual Foxpro Driver}; SourceDB=C:\PRACTICE\DBF; SourceType=DBF;"
        Dim cmd As OdbcCommand = New OdbcCommand
        oOBCConnection = New OdbcConnection(sConnectionString)
        oOBCConnection.Open()

        Console.WriteLine("ODBC Connection Establsihed")

        For Each eleRow As DataRow In DT.Rows

            'Check if the record exists in the targeted database
            ==================================================
           My dbf table name is SALES and got only field named Serialno
             ==================================================
            Dim myCommand As New OdbcCommand("SELECT COUNT(*) FROM SALES WHERE Serialno = @Serialno", oOBCConnection)
            myCommand.Parameters.Add("@Serialno", eleRow("Serialno"))
            Dim intCount As Integer = CType(myCommand.ExecuteScalar, Integer)
            If intCount = 0 Then
                ' insert the missing value
                myCommand.CommandText = "INSERT INTO SALES (Serialno) VALUES (@Serialno)"
                myCommand.ExecuteNonQuery()
            End If
            myCommand.Dispose()
        Next

        Console.WriteLine("Successfully Completed")

Pls help

Does case sensitivity giving problem?

My SQL server table field names is SERIALNO
dbf filed name is Serialno

SalesS is the sql server table  and SalesD is my dbf table

nyee84

First, a correction to what I said above.  I was getting confused.  Both of the commands in my code are ODBC not one SQL and one ODBC.  For some reason, I was thinking when I typed that that you were querying one table and inserting to the other, but that is not the case - and I knew that.  Sorry for the additional confusion I may have caused to you.

Turning to the current questions, I am now a bit confused.  Is the table in DBF called Sales or SalesD?  Whichever it is should be the tablename in this line

            Dim myCommand As New OdbcCommand("SELECT COUNT(*) FROM SALES WHERE Serialno = @Serialno", oOBCConnection)
 
and in this line

                myCommand.CommandText = "INSERT INTO SALES (Serialno) VALUES (@Serialno)"

I don't think case should matter, but I usually try to use the same case in the commandtext as is used in the database.

The other thing I think you should do, in both those lines, is replace @Serialno by ?.  As I said earlier ODBC ignores parameter names, and uses ? as the marker for where parameters appear in commandtexts.

Roger

Here's a quote from the Help file on that last point

>>
The ODBC .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OdbcCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

As a result, the order in which OdbcParameter objects are added to the OdbcParameterCollection must directly correspond to the position of the question mark placeholder for the parameter.
<<

Roger

Roger,

Had  some misunderstanding occured.

The purpose of this program is to copy entrire serial no from a sql server table named SalesS whhere the filename which we need to copy the serialno is SERIALNO.

So i will copy all the serialno from SalesS into a dbf tables called SalesD and that dbf table to also got a field named
Serialno.

While moving the records into SalesD from SalesS, i need to avoid dupicates.

I have to loop throgh my destination table SakesD, if the same record already exist in SalesD, should aviod copying the duplicate record into SalesD.

i thought of using ur first piece of code, seems like it is not really satisfying my requirement.

Correct me if am wrong..

Thanks anyway, Roger..
 

Correction... whhere the fieldname which we need to copy the serialno is SERIALNO.

You are the only one who can know whether a piece of code is satisfying your requirements.  Here is the code with, so far as I understand them, specific names/types in it.  I have guessed that the SerialNo is an Integer.

    Private Sub doSerialNo()
        Dim DT As New DataTable
        Dim ConnectionString As SqlConnection = New SqlConnection("Server = xxx; user id=yy; pwd=zz; Database=sales")
        Dim DA As New SqlDataAdapter("select SerialNo From SalesS", ConnectionString)
        DA.Fill(DT)
        Dim oODBCConnection As OdbcConnection
        Dim sConnectionString As String = "Driver={Micrsoft Visual Foxpro Driver}; SourceDB= C:\Test;SourceType=DBF;"
        oODBCConnection = New OdbcConnection(sConnectionString)
        oODBCConnection.Open()
        Dim cmdTest As OdbcCommand = New OdbcCommand
        cmdTest.Connection = oODBCConnection
        cmdTest.CommandText = "SELECT COUNT(SerialNo) FROM SalesD WHERE SerialNo = ?"
        cmdTest.Parameters.Add("@SerialNo", OdbcType.Int)
        Dim testResult As Integer
        Dim cmdInsert As OdbcCommand = New OdbcCommand
        cmdInsert.Connection = oODBCConnection
        cmdInsert.CommandText = "INSERT INTO SalesD (SerialNo) VALUES (?)"
        cmdInsert.Parameters.Add("@SerialNo", OdbcType.Int)
        For Each dr As DataRow In DT.Rows
            Try
                cmdTest.Parameters("@SerialNo").Value = dr("SerialNo")
                testResult = cmdTest.ExecuteScalar
                Try
                    If testResult = 0 Then
                        cmdInsert.Parameters("@SerialNo").Value = dr("SerialNo")
                        cmdInsert.ExecuteNonQuery()
                    End If
                Catch ex As Exception
                    MsgBox("Error writing" & vbCrLf & ex.Message)
                End Try
            Catch ex As Exception
                MsgBox("Error reading" & vbCrLf & ex.Message)
            End Try
        Next
        oODBCConnection.Close()
    End Sub

I cannot actually test it without constructing dummy SQL and DBF databases but it compiles OK.  Can I suggest that you copy and paste it as it stands and try it.  I have added a couple of try/catch blocks so that, if there is something wrong with the code, you should be able to report back precisely what the errors are.  If It runs OK but produces the wrong results - e.g. missing some records out, or duplicating some records - if you describe, in detail, what is wrong about those results, we will try to identify what needs to be changed.

Roger

Ok, Thanks, let me digest your code..

Roger thanks alot ..