• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1797
  • Last Modified:

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

Hi Experts ,

I had piece of which will open connection to a sql server and table and also to a dbf table.

Somereason, i need to copy all the records from sql server table into dbf table. But some recodrs already exist in dbf table so i dont want to copy again, here is the the code i written

Imports System.Data.SqlClient
Imports System.Data.Odbc

Module Module1

    Sub Main()

        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 sales", ConnectionString)
        DA.Fill(DT)
        Dim oODBCConnection As OdbcConnection
        Dim sConnectionString As String = "Driver={Micrsoft Visual Foxpro Driver}; SourceDB= C:\Test;SourceType=DBF;"
        Dim cmd As OdbcCommand = New OdbcCommand
        oODBCConnection = New OdbcConnection(sConnectionString)
        oODBCConnection.Open()
        End Sub
       My dbftable salesdbf got only onefield i which is SerialNo and i need to copy all the SerialNo from sales table and need to put in dbf table.. need avoid copying duplicate records means do want to copy the record which is already in the dbf table. Pls help me to complete the code..

   

0
nyee84
Asked:
nyee84
  • 12
  • 8
  • 2
2 Solutions
 
SanclerCommented:
Here's two possible approaches.

Loop through the source datatable testing each row in turn against the target table in the database.  If it already exists in that, do nothing: otherwise save it to the database.  That would be on these lines

        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 <TableName> WHERE SerialNo = ?"
        cmdTest.Parameters.Add("@SerialNo",<myDataType>)
        Dim testResult As Integer
        Dim cmdInsert As OdbcCommand = New OdbcCommand
        cmdInsert.Connection = oODBCConnection
        cmdInsert.CommandText = "INSERT INTO <TableName> (SerialNo) VALUES (?)"
        cmdInsert.Parameters.Add("@SerialNo",<myDataType>)
        For Each dr As DataRow in DT.Rows
             cmdTest.Parameters("@SerialNo").Value = dr("SerialNo")
             testresult = cmdTest.ExecuteScalar
             If testResult = 0 Then
                  cmdInsert.Parameters("@SerialNo").Value = dr("SerialNo")
                  cmdInsert.ExecuteNonQuery()
             End If
        Next
        oODBCConnection.Close()

Note that I've coded that on the fly.  It has not been tested.  The general structure is OK, I think.  But you will need to check the detailed syntax.

The other would be to bring over the data from the dbf database into a second datatable, use the function below to return a "difference" table, and then save that back to the dbf database.  The function is from one of my utility modules and has been tested.

    Public Function Difference(ByVal tb1 As DataTable, ByVal tb2 As DataTable, ByVal pk As String) As DataTable

        'Assumes input datatables have same structure and that ...
        '... pk (which is a column/field name) is common to both ...
        '... and its values are unique within both.  It will ...
        '... normally be the name of the Primary Key field

        'NOTE: It is important in which order tables are put ...
        '... in arguments.  Function returns those rows in tb2 ...
        '... which do not appear in tb1

        Dim dra() As DataRow
        Dim dr As DataRow
        Dim sb As New System.Text.StringBuilder(tb1.Rows.Count * 8)
        '... the * 8 is arbitrary, but it's unlikely that ...
        '... any pk value will be shorter than 4 and then ...
        '... there's the quotes, comma and space
        Dim ftr As String
        Dim result As New DataTable
        'give new table correct structure
        result = tb1.Clone
        'collect pk values for one table for filter string
        For Each dr In tb1.Rows
            sb.Append("'" & dr(pk) & "', ")
        Next
        'use string builder result to make filter string
        ftr = pk & " NOT IN (" & sb.ToString.Substring(0, sb.ToString.LastIndexOf(",")) & ")"
        'filter other table with it
        dra = tb2.Select(ftr)
        'put results into new table
        For Each dr In dra
            result.ImportRow(dr)
        Next
        Return result

    End Function

Roger
0
 
LordWabbitCommented:
Here we go, havent run it obviously, but it does compile so give it a try and let me know if you have any issues

        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 sales", ConnectionString)
        DA.Fill(DT)
        Dim oODBCConnection As OdbcConnection
        Dim sConnectionString As String = "Driver={Micrsoft Visual Foxpro Driver}; SourceDB= C:\Test;SourceType=DBF;"
        Dim cmd As OdbcCommand = New OdbcCommand
        oODBCConnection = New OdbcConnection(sConnectionString)
        oODBCConnection.Open()
        For Each eleRow As DataRow In DT.Rows
            ' check if the record exists in the target database
            Dim myCommand As New OdbcCommand("SELECT COUNT(*) FROM SALES WHERE SerialNo = @SerialNo", oODBCConnection)
            myCommand.Parameters.AddWithValue("@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
0
 
nyee84Author Commented:

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....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SanclerCommented:
AddWithValue is new in Framework 2.0

Roger
0
 
nyee84Author Commented:
I am using framework 1.1 & I dont have visualstudio too.

How should i solve the issue if i use LordWabbit Code.
0
 
SanclerCommented:
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
0
 
nyee84Author Commented:
ok, i will try ur code. Thanks

0
 
nyee84Author Commented:
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
0
 
nyee84Author Commented:
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
0
 
LordWabbitCommented:
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
0
 
SanclerCommented:
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
0
 
SanclerCommented:
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
0
 
nyee84Author Commented:
"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



0
 
nyee84Author Commented:
Does case sensitivity giving problem?

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

0
 
nyee84Author Commented:
SalesS is the sql server table  and SalesD is my dbf table
0
 
SanclerCommented:
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
0
 
SanclerCommented:
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
0
 
nyee84Author Commented:
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..
 
0
 
nyee84Author Commented:
Correction... whhere the fieldname which we need to copy the serialno is SERIALNO.
0
 
SanclerCommented:
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
0
 
nyee84Author Commented:
Ok, Thanks, let me digest your code..

0
 
nyee84Author Commented:

Roger thanks alot ..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 12
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now