Issues with DataAdapters

[dilbert0610]

Ok. I am just starting to do some programming for my work. My boss came to me with a simple request to see if I was able to do it. Well I get almost to the end of his request and then I get stuck. Here is what was needed.

I need to go through our parts list in a table named IMINVLOC_SQL (this is in pervasive) I need to find all parts that have "Reorder_Lvl" set to 0 and "Ord_Up_To_Lvl" set to 1.

This is what I came up with:

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] GetInfo()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] count [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowCount [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2]rowCount = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcConnection[/SIZE]
[SIZE=2]myConn.ConnectionString = "ServerDSN=testmac;TransportHint=TCP:SPX;DSN=testmac;ArrayBufferSize=8;ArrayFetchOn=1;ServerName=manmac.1583;UID=user;DecimalSymbol=."[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT * FROM IMINVLOC_SQL WHERE Reorder_Lvl = 0 AND Ord_Up_To_Lvl = 1"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ad [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcCommand(sql, myConn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]myConn.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcDataAdapter[/SIZE]
[SIZE=2]da.SelectCommand = ad[/SIZE]
[SIZE=2]da.Fill(ds, "MyData")[/SIZE]
[SIZE=2]count = ds.Tables(0).Rows.Count[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE]
[SIZE=2][SIZE=2]ds.Tables(0).Rows(rowCount).Item(10) = 1[/SIZE]
[/SIZE][SIZE=2]rowCount = rowCount + 1[/SIZE]
[SIZE=2]count = count - 1[/SIZE]
[SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][SIZE=2] count = 0[/SIZE]
[SIZE=2][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]da.Update(ds.Tables(0))[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] x [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MsgBox("didnt work")[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
myConn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

The problem I am having is that I do not have the update, select, and insert statments set. My problem is I do not know how to set them. I guess in order for this to actually update the main database with the data in the dataset i need the update statement set. I know the sql command to do it, but I do not know how to go about figuring out how to say which rows need updated and how to tell the statment which ones it needs to update.

I found a way around the problem just to get the task done however I would like to understand how this is supposed to work for future times. This is what I used to get it to work. I know it is not the way it is supposed to be done but it worked for this time.

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] GetInfo()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] count [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowCount [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2]rowCount = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcConnection[/SIZE]
[SIZE=2]myConn.ConnectionString = "ServerDSN=testmac;TransportHint=TCP:SPX;DSN=testmac;ArrayBufferSize=8;ArrayFetchOn=1;ServerName=manmac.1583;UID=user;DecimalSymbol=."[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT * FROM IMINVLOC_SQL WHERE Reorder_Lvl = 0 AND Ord_Up_To_Lvl = 1"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ad [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcCommand(sql, myConn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]myConn.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcDataAdapter[/SIZE]
[SIZE=2]da.SelectCommand = ad[/SIZE]
[SIZE=2]da.Fill(ds, "MyData")[/SIZE]
[SIZE=2]count = ds.Tables(0).Rows.Count[/SIZE]
[SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] updatesql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "UPDATE IMINVLOC_SQL SET Reorder_Lvl = '1' Where Item_No = '" & ds.Tables(0).Rows(rowCount).Item(0) & "'"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] update [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcCommand(updatesql, myConn)[/SIZE]
[SIZE=2]update.ExecuteNonQuery()[/SIZE]
 
 
 
 
[SIZE=2]rowCount = rowCount + 1[/SIZE]
[SIZE=2]count = count - 1[/SIZE]
[SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][SIZE=2] count = 0[/SIZE]
[SIZE=2]dg.DataSource = ds[/SIZE]
[SIZE=2]myConn.Close()[/SIZE]
[SIZE=2]lblCounter.Text = count[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

I would love if someone could explain to me how to generate the sql statements for setting the update, select, and insert for the dataadapter.


I also hope I put this is the right spot. I am sure it could have gone in the ODBC but it looks pretty dead in that board..

 

[BadgerByte]

Hey dilbert0610,

The quickest way to see how this is done is to use the wizard and view the code it generates. ie open a form drag on an odbc dataadapter and follow the instructions. Then go to code view and look at the windows generated code. (Or you could just use the generated dataadapters).

Hope this helps,

Regards,

Andy

 

[dilbert0610]

Thanks. I will give that a shot.

 

[aBsOlUt]

dilbert0610,

Since you know the update SQL command, which according to your original post is:

[SIZE=2]UPDATE IMINVLOC_SQL SET Reorder_Lvl = '1' Where Item_No = '" & ds.Tables(0).Rows(rowCount).Item(0) & "'"
[/SIZE]

The dataAdapter comes with an Update command object attached to it. You'd be better off if you do the following:

            Dim updCmd As New OleDb.OleDbCommand
            updCmd.Connection = Myconn
            updCmd.CommandText = "UPDATE IMINVLOC_SQL SET Reorder_Lvl = '1' Where Item_No = '" & ds.Tables(0).Rows(rowCount).Item(0) & "'"""
            updCmd.CommandType = CommandType.Text

            da.UpdateCommand = updCmd
            'Then call the dataAdapters Update() method
           da.Update()

It should execute your updCmd object as its now linked to it. If its not you will get an exception. In very similar fashion the Insert and Select commands work the same.

You could also use a commandBuilder object that generates them on the spot but might not fit your needs...or you can go with what BadgerByte suggested.