I have written some code using a datareader and so far it is working fine. However my sql string is getting rather cumbersome and difficult to read and it seems sensible to use a stored procedure instead, especially since I will be extending the search criteria on the select statement.
This is the code I have at the moment:
Dim cnRHG As New SqlConnection(My.Settings.Red_Hot_Group_SystemConnectionString)
Dim strSQLPossDupes As String
strSQLPossDupes = "SELECT Customer_Name.Cust_Name_Family,Customer_Name.Cust_Name_Given_1, Customer_Name.Cust_Name_Given_2 FROM Customer_Name WHERE Customer_Name.Cust_Name_Family = " + strSurnameSearch
Dim cmPossDupes As New SqlCommand(strSQLPossDupes, cnRHG)
Try
cnRHG.Open()
Dim sdrPossDupes As SqlDataReader = cmPossDupes.ExecuteReader
If sdrPossDupes.HasRows Then
While sdrPossDupes.Read
tblPossDupes.Rows.Add(sdrPossDupes(0), sdrPossDupes(1), sdrPossDupes(2))
End While
End If
Catch ex As Exception
MsgBox("error getting data")
End Try
The problem I am having is that whilst I have created a stored procedure and tested to ensure it returns the expected results I cannot work out how to call it (along with the search parameters) to get the data into the datareader. Can anyone offer some help please
Regards
matt
Datareader and Stored procedure.
Boris Mueller
In addition to what the others have said, the advantage of using the parameters is that you will usually be able to avoid SQL Injection. Using the method of building SQL statements dynamically as you have done opens yourself up to potential hackers being able to get full reign on your database.
Jim Wooley
http://devauthority.com/blogs/jwooley
voila jacques
AlexBB
Jamie Thomson
Many thanks to those of you who replied. You gave me enough to get started and I now have code that calls stored procedures.
Warm regards
Matt
Danny Tuppeny
Its actually very easy (and a lot nicer to read)...
You need to set the CommandType of the SQLCommand to Stored Procedure and then add the Parameters using the method AddWithValue method (see below)
Dim o As SqlClient.SqlCommando.CommandType = CommandType.StoredProcedure
o.Parameters.AddWithValue(
"@Para1", "Parameter value")Once you've set the parameters, you just execute your command in the same way you do when using your sqlstring. Hope this helps!