dilbert0610
Member
- Joined
- Aug 3, 2006
- Messages
- 22
- Programming Experience
- Beginner
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:
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.
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..
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:
VB.NET:
[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.
VB.NET:
[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..