Solved

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

Posted on 2006-07-18
22
1,729 Views
Last Modified: 2012-08-13
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
Comment
Question by:nyee84
  • 12
  • 8
  • 2
22 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 17136589
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
 
LVL 11

Assisted Solution

by:LordWabbit
LordWabbit earned 200 total points
ID: 17136762
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
 

Author Comment

by:nyee84
ID: 17137575

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
 
LVL 34

Expert Comment

by:Sancler
ID: 17137759
AddWithValue is new in Framework 2.0

Roger
0
 

Author Comment

by:nyee84
ID: 17139097
I am using framework 1.1 & I dont have visualstudio too.

How should i solve the issue if i use LordWabbit Code.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17139344
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
 

Author Comment

by:nyee84
ID: 17140036
ok, i will try ur code. Thanks

0
 

Author Comment

by:nyee84
ID: 17143864
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
 

Author Comment

by:nyee84
ID: 17143897
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
 
LVL 11

Expert Comment

by:LordWabbit
ID: 17144431
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17144511
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:Sancler
ID: 17144525
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
 

Author Comment

by:nyee84
ID: 17144690
"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
 

Author Comment

by:nyee84
ID: 17144710
Does case sensitivity giving problem?

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

0
 

Author Comment

by:nyee84
ID: 17144727
SalesS is the sql server table  and SalesD is my dbf table
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17144796
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17144804
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
 

Author Comment

by:nyee84
ID: 17151143
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
 

Author Comment

by:nyee84
ID: 17151148
Correction... whhere the fieldname which we need to copy the serialno is SERIALNO.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17152383
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
 

Author Comment

by:nyee84
ID: 17152514
Ok, Thanks, let me digest your code..

0
 

Author Comment

by:nyee84
ID: 17182152

Roger thanks alot ..
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now