IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 BEGIN SET @BikeCount = (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.' PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.'; END ELSE BEGIN SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ); PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ; END ; GO
Jun
14
2010
Category : ADO / SQL
May
04
2010
THE KEY LINE BELOW IS : SqlCommandBuilder.DeriveParameters(cmd);
SqlConnection c = new SqlConnection(TextboxConnString.Text); //NAME OF STORED PROC string sql = DropdownStoredProcs.Value.ToString(); SqlCommand cmd = new SqlCommand(sql, c); cmd.CommandType = CommandType.StoredProcedure; c.Open(); SqlCommandBuilder.DeriveParameters(cmd); foreach (SqlParameter param in cmd.Parameters) { if ((param.Direction == ParameterDirection.Input) || (param.Direction == ParameterDirection.InputOutput)) { //Notice that I can get the Name of the parameter as well as it's DBType... string paramDescription = param.ParameterName + " | " + param.SqlDbType.ToString(); DropdownSPParameters.Items.Add(paramDescription, param.ParameterName); } } c.Close();
May
04
2010
This particular example fills a dropdown list with the list of Stored Procedures and takes it’s connection string from a textbox.
SqlConnection c = new SqlConnection(TextboxConnString.Text); c.Open(); string sql = "SELECT name AS spname FROM sysobjects WHERE (xtype = 'p') ORDER BY name"; SqlCommand cmd = new SqlCommand(sql, c); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dtStoredProcs = new DataTable(); adapter.Fill(dtStoredProcs); if (dtStoredProcs.Rows.Count > 0) { DropdownStoredProcs.Items.Clear(); foreach (DataRow dr in dtStoredProcs.Rows) DropdownStoredProcs.Items.Add(dr["spname"].ToString(), dr["spname"]); } c.Close();
Jul
22
2009
Category : ADO / SQL
BEGIN TRAN
–Delete from table.
DELETE FROM blah where blah
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return -1
END
–NOW delete from other table
DELETE FROM blah2 where blah2
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return -1
END
COMMIT TRAN
Jul
08
2009
Category : ADO / SQL
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
Jul
06
2009
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!
Jun
30
2009
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
Jun
04
2009
I had a datatable that I needed to sort by a certain column. Here is what I did, if you have a better idea, please let me know!
'Set up the variables for the sort: Const Sort_Column_Key As String = "Test Order" 'This is your column name Const Sort_Direction As String = " ASC" Const Sort_Format As String = "{0} {1}" 'Sort the chartData dataTable so we print the data in the right order - This sorts chartData.DefaultView.Sort = String.Format(Sort_Format, Sort_Column_Key, Sort_Direction) 'Now for me to use it in my future FOR EACH loop, I had to create a new table to put the sorted data into... GOT A BETTER WAY? Let me know! Dim chartData2 As DataTable = chartData.DefaultView.ToTable
This worked well for me! I hope it helps someone else! t
Jun
04
2009
You want to add a column to a dataTable:
Dim testColumn As New DataColumn
Now you want to change the dataType (default is a string):
testColumn.DataType = Type.GetType(“System.Int32″)
Ya, nice and simple right… (would it have been too hard just to say ‘= Integer’???
(“System.String”) is the string…beyond that, you tell me.
Mar
18
2009
dim newDataset as dataset
newDataset = oldDataset.clone()
