Data Adapter resources?

[jcoyan]

Is anyone familar of a resource I could use, to get familar with Data Adapters? I've read all material I can find, but can't find a solid way to do what I need to do.

I have a datatable that is filled from a data adapter. The datatable is then set as the source of a datagrid. I need users to enter data into this grid, then either when a button is clicked or when the grid is exited, the data will update on the actual database.

I'm not looking for someone to tell me every step I need to take, just perhaps point me in the direction of a nice tutorial, or example code.

Thanks in advance!

 

[vis781]

First of all you really should be using the same dataadapter.

 Dim sqlString As String
            sqlString = ""
            Dim myDataAdapter As New SqlDataAdapter(sqlString, aConnection)
            myDataAdapter.InsertCommand.CommandText = "INSERT INTO " & 
currentTableError & " (ErrorCode,Description,StartTime,EndTime,Waiting) " & _
                       " VALUES (ErrorCode,Description,StartTime,EndTime,Waiting)"
 
            myDataAdapter.Update(myDataSet.GetChanges, currentTableError)

Currenttableerror is the name of your datatable? Then why are you passing the actual object into the SQL

myDataAdapter.InsertCommand.CommandText = "INSERT INTO " &
currentTableError & " (ErrorCode,Description,StartTime,EndTime,Waiting) " & _
" VALUES (ErrorCode,Description,StartTime,EndTime,Waiting)"

a SQL statement should take the actual name of the database table as part of the SQL string...

"INSERT INTO Mytable .......  VALUES ......"

Here, these are variables that hold the information that you want to insert?
Your trying to pass string literals into an SQL statement. These should either be parameter placeholders or the actual data you want to insert.

(ErrorCode,Description,StartTime,EndTime,Waiting)"

Get rid of the getchanges and just put the datatable in here.

myDataAdapter.Update(myDataSet.GetChanges, currentTableError)

Also i suggest you read some articles on how to perfom database queries. There should be plenty about if you google for ADO.Net

 

[jcoyan]

I can set it up to use the same adapter.

"currentTableError" is a dynamic variable. It holds the current table for errors. This table can change and I need a way to track that change, so I use this variable. It will always contain the name of the table I need to update.

As for the variables I want to insert, that's what I'm asking. What should my Insert statement look like? I want to insert anything that was entered into my datagrid. From what I understand, when I call the Update method of my data adapter, VB will automatically use the InsertCommand if I'm inserting a a new record. I assumed that I had to create this Insert command on my own, except I don't know what my "VALUES" are, because I'm not explicitly setting them, I'm letting VB handle it via the datagrid.

The only thing I have left to have this working, is to push out the changes when the user modifies the datagrid. I suppose I'm just a little confused on how to do that.

 

[jcoyan]

I don't really know what's going on.

I declared the data adapter in a module, as Friend, as I do with all variables I absolutely need to be global.

I can reference myDataAdapter and change the SelectCommand when clicking 5 different buttons within my program.

Yet when I try to do anything to myDataAdapter when I click my "Update" button, I get the object reference error.

 

[jcoyan]

Alright, I finally got everything figured out EXCEPT the InsertCommand.

It's telling me I need a valid one, but since I'm updating from the datagrid and not explicitly setting variables with values to add, I'm not quite sure how to format it...

Any help?

 

[vis781]

Something like..

INSERT INTO columns...... VALUES @fieldname, @FieldName,

Add all the placeholders to the query for as many fields there are in the table then you need to add parameters to the sqlcommands parameter collection..

YourSqlCommand.Parameters.add(.....)

Add all the parameters for each placeholder and in the order that they appear in the query. Here's a link with some good info

http://builder.com.com/5100-6371_14-6093390.html

 

[jcoyan]

Seems that was exactly what I was looking for. Do you have any other resources?

 

[vis781]

There are literally hundreds of articles on the web, and MSDN.

 

[jcoyan]

But it's hard to find them if you don't know what you're looking for...

 

[vis781]

Ok here's an example....

con = New SqlConnection("Data Source=mysource;Initial Catalog=mydbname;uid=sa")
'create sql adapter for the "emp" table
Dim sqlDa As SqlDataAdapter = New SqlDataAdapter("select * from emp", con)
'create dataset instance
Dim dSet As DataSet = New DataSet()
'fill the dataset
sqlDa.Fill(dSet, "emp")
'bind the data grid with the data set
dataGrid1.DataSource=dSet.Tables("emp")
'build select command
Dim selCmd As SqlCommand = New SqlCommand("select * from emp",con)
sqlDa.SelectCommand=selCmd
'build insert command
Dim insCmd As SqlCommand = New SqlCommand("insert into emp (Name, Age) values(@Name, @Age)",con)
insCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name")
insCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age")
sqlDa.InsertCommand = insCmd
'build update command
Dim upCmd As SqlCommand = New SqlCommand("update emp set [EMAIL="Name=@Name"]Name=@Name[/EMAIL], [EMAIL="Age=@Age"]Age=@Age[/EMAIL] where <A href="mailto:No=@No",con">No=@No",con)
upCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name")
upCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age")
upCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No")
sqlDa.UpdateCommand = upCmd
'build delete command
Dim delCmd As SqlCommand = New SqlCommand("delete from emp where <A href="mailto:No=@No",con">No=@No",con)
delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No")
sqlDa.DeleteCommand = delCmd
'now update the data adapter with dataset.
sqlDa.Update(dSet,"emp")

http://www.codeproject.com/cs/database/sqldawithoutsqlcb.asp

http://www.developersdex.com/gurus/code/859.asp

Or of course you can just use the 'CommanBuilder' have a look at what it generates and then get an idea of how to go about it from there.

 

[jcoyan]

I get the idea behind it and have setup everything, but I'm getting a strange error.

            Dim myCommand As New SqlCommand("INSERT INTO " & currentTableError & " (ErrorCode,Description,StartTime,EndTime,Waiting) " & _
                       " VALUES (@ErrorCode,@Description,@StartTime,@EndTime,@Waiting)")
            myCommand.Parameters.Add("@ErrorCode", SqlDbType.Int, "ErrorCode")
            myCommand.Parameters.Add("@Description", SqlDbType.VarChar, "Description")
            myCommand.Parameters.Add("@StartTime", SqlDbType.VarChar, "StartTime")
            myCommand.Parameters.Add("@EndTime", SqlDbType.VarChar, "EndTime")
            myCommand.Parameters.Add("@Waiting", SqlDbType.NVarChar, "Waiting")

I get this when I try to add the first parameter.
"Cast from String 'ErrorCode' to type Integer is not valid."

Which is weird, because as far as I can tell, there is no where that ErrorCode should be perceived as a String. I double checked the column on the SQL Server just to be sure.

 

[vis781]

You've missed out the size argument of the parameter. The reason your getting that error is because the the third argument for a parameter is an integer that is the size of the field.

  myCommand.Parameters.Add("@ErrorCode", SqlDbType.Int, 0,"ErrorCode")
            myCommand.Parameters.Add("@Description", SqlDbType.VarChar, 255,  "Description")
            myCommand.Parameters.Add("@StartTime", SqlDbType.VarChar, 255, "StartTime")
            myCommand.Parameters.Add("@EndTime", SqlDbType.VarChar, 255, "EndTime")
            myCommand.Parameters.Add("@Waiting", SqlDbType.NVarChar, 255, "Waiting")

 

[jcoyan]

Thank you so much for all the time you've taken to help me out with this. I FINALLY got the Insert to work, but I can figure the others out on my own.

I really appreciate it so much.

Thank you!!!

 

[jcoyan]

I take that back. While I have the UPDATE query setup, it is updating every row to reflect the one row I change, due to the fact that I do not have a WHERE clause.

Now, I am updating a child table, with no unique identifier other than the primary key. If I had a way to query for this key (by searching on two other fields, or whatever), I'm assuming I could use the something along the lines of:

...WHERE PrimaryKey = ' & primaryKey & '

and it would update each line on an individual basis. However, without modifying the structure of every child table, I don't seem to have that option.

Does VB have a simple way to only update fields that were changed? Here is my update statement as is.

            Dim myUpdateCommand As New SqlCommand("UPDATE [" & currentTableError & "] SET ErrorCode = @ErrorCode,Description = @Description, " & _
                       " StartTime = @StartTime,EndTime = @EndTime,Waiting = @Waiting")
            myUpdateCommand.Parameters.Add("@ErrorCode", SqlDbType.Int, 255, "ErrorCode")
            myUpdateCommand.Parameters.Add("@Description", SqlDbType.VarChar, 255, "Description")
            myUpdateCommand.Parameters.Add("@StartTime", SqlDbType.VarChar, 255, "StartTime")
            myUpdateCommand.Parameters.Add("@EndTime", SqlDbType.VarChar, 255, "EndTime")
            myUpdateCommand.Parameters.Add("@Waiting", SqlDbType.NVarChar, 255, "Waiting")

            myDataAdapter.UpdateCommand = myUpdateCommand
            myDataAdapter.UpdateCommand.Connection = aConnection
            myDataAdapter.Update(myDataSet, currentTableError)

 

[vis781]

Yes your on the right track, your where clause would look something like this....

WHERE PrimaryKeyField = @PKey

Then the parameter for that would go.....

myUpdateCommand.Parameters.Add("@PKey", FieldType, FieldSize, FieldName).SourceVersion = DataRowVersion.Original

This method uses optimistic concurrency, so your less likely to get update conflicts.

 

[jcoyan]

Where do you find this information? I have searched the web, but only found bits and pieces, detailing one small portion of what we've discussed here. I've checked MSDN as well, but generally only find the "basics" of whatever class I'm working with.

I may be looking in the wrong places, I was just curious if your information came from a book, or simply from years of coding.

Either way, I really appreciate the time you've take to help me out with this. I had been struggling with this for a couple weeks, and it was one of the main pieces of my program I had yet to complete.

Thank you again, the help is much appreciated.