The other day I was trying to update a row and thought I would use the ‘.ExecuteNonQuery’ method in vb.net using the Enterprise Library.
The method tells me that it will send me back an integer indicating how many rows were affected. Well it didn’t. It was sending me back a ‘-1′ everytime even though the Stored Procedure was running properly.
So I went to SQL to see what was happening on its end if I executed it.
Strangely, I was getting a Result of 0 and a message that 1 row was affected when I ran the SP (the 0 was strange, I dont know why it sends that back). EVEN STRANGER things happened when I intentionally put in a bogus ID so that the UPDATE state would not work. I got back the same result! I got a 0 returned and I got 1 row affected, EVEN THOUGH I KNOW that there were no rows affected.
Well that’s my story, so this is what I did inside my Stored Procedure to solve this issue:
=================================================================
=================================================================
BEGIN
–YOU CAN CREATE AN IF ELSE STATEMENT LIKE THIS.
IF EXISTS ( SELECT userID FROM users WHERE UserID=@UserID)
–NOW I KNOW THAT MY ID EXISTS – SO I PERFORM MY UPDATE AND SEND BACK A 1!
BEGIN
UPDATE tbl_user
SET S-ID=@s-ID
WHERE UserID = @UserID
SELECT 1
END
ELSE
BEGIN
‘I KNOW NOW THAT I DID NOT HAVE A PROPER ID – SO SEND BACK A 0
SELECT 0
END
END
==============================================================
==============================================================
After doing this I simply needed to change my code from an ExecuteNonQuery to an ExecuteScalar and check for a 1 or a 0.
Hope this helps!
t
Here is one cool way to get a string into a dataset:
First build your string so that is correctly formatted XML. For instance:
Dim strXCustInfo As String = "<Root><CusTable><Name>Todd </Name><Address>123 Fourth Street</Address></CusTable>" & _
"<CusTable><Name>Dawn </Name><Address>432 Washington Ave</Address></CusTable></Root>"Now that we have this, we need to convert the string into a Stringbuilder.
Dim srCustInfo As New StringReader(strXCustInfo )
Now create your dataset and call the .ReadXml method on the dataset to get the above string right into a ready to be stored dataset! WOOT!
Dim ds As New DataSet
ds.ReadXml(srCustInfo)
Hope this helps someone!
Below is an example of how to grab all your table names from your database:
*YOU WILL NEED TO IMPORT System.Data.SqlClient for this code.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a table to hold the table names
Dim tables As DataTable = New DataTable("Tables")
'Create your connection string and include it in your connection
Using sqlConn As New SqlConnection(constring)
sqlConn.Open()
'This is the sql statement that will bring back your table names
Dim sql As String = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'"
'Command to get the names
cmd = New SqlCommand(sql, sqlConn)
adapter = New SqlDataAdapter(cmd)
'THIS FILLS YOUR TABLE WITH THE TABLE NAMES!
adapter.Fill(tables)
'AT THIS POINT you will have a data table that is filled your table names from your database.
End Using
End SubCheers!
t
dim newDataset as dataset
newDataset = oldDataset.clone()
I had a dataTable with three columns
Name | Rank | Percentage
For my own personal reasons, I needed the Percentage to be the second column and not the third. Here’s the code to do the trick.
myDataTable.Columns(2).SetOrdinal(1)
Sounds easy enough… of course, NOT! Try it a normal way and you will be scolded for trying to add a row that already belongs to another table… JEEZ WHAT WERE YOU THINKING! (God forbid, it just know that I want it copied….freaking reference types.)
Anyway, here’s how you make add a new row to a datatable that is the same as the row in a different datatable.
‘CREATE THE ROW
Dim newRow As DataRow = finalTable.NewRow
‘COPY the ITEMS from your OLD ROW
newRow.ItemArray = OLDrow.ItemArray
‘ADD THE ROW (again… I mean what the heck does finaltable.NewRow do!?!?!?!)
finalTable.Rows.Add(newRow)
===================================================
And that’s it… another nugget.
