SQL stored procedures

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I have built a windowsform application. I want to insert a new record into a
SQL database using a stored procedure. When I run the app I get an error
message "Line 1: Incorrect syntax near 'usp_AddData'". When I exec the
stored procedure in Query Analyser it works. I have a similar app where the
the same code works without errors. I have compare both apps and have not
been able to find a difference. Any ideas?
Here is the code I use if it helps:

Public CnStr As String = "packet size=4096;integrated security=SSPI;data
source=""(local)"";persist security info=False;initial catalog=LakeSurvey"

Public Cn As New SqlConnection(CnStr)

Public Sqlcm As SqlClient.SqlCommand


Private Sub btnSaveLakeData_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSaveLakeData.Click

Try

Dim Cn As New SqlConnection(CnStr)

Dim SqlCm As SqlClient.SqlCommand = New
SqlClient.SqlCommand("usp_AddLakeData1", Cn)

Dim prmLD1 As SqlClient.SqlParameter = SqlCm.Parameters.Add("@HydroID",
SqlDbType.Int, 4)

Dim prmLD2 As SqlClient.SqlParameter = SqlCm.Parameters.Add("@SurveyDate",
SqlDbType.SmallDateTime, 4)

prmLD1.Value = Me.lblHydroID.Text

prmLD2.Value = Me.dtSurveyDate.Value

Cn.Open()

SqlCm.ExecuteNonQuery()

Cn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub
 
Try assigning the CommandType for the SQL command. Your other code was
probably using dynamic SQL.

Dim SqlCm As SqlClient.SqlCommand = _
New SqlClient.SqlCommand("usp_AddLakeData1", Cn)
SqlCm.CommandType = CommandType.StoredProcedure

Also, you probably should not Dim the cn and SqlCm variables both at the
module and procedure level. Procedure level is fine.

I would suggest looking into downloading and incorporating the Microsoft
Data Application Block for "best practices".

http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
 
I completely missed that. Thank you

jd
Jim Hughes said:
Try assigning the CommandType for the SQL command. Your other code was
probably using dynamic SQL.

Dim SqlCm As SqlClient.SqlCommand = _
New SqlClient.SqlCommand("usp_AddLakeData1", Cn)
SqlCm.CommandType = CommandType.StoredProcedure

Also, you probably should not Dim the cn and SqlCm variables both at the
module and procedure level. Procedure level is fine.

I would suggest looking into downloading and incorporating the Microsoft
Data Application Block for "best practices".

http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp


into
Click to expand...
 
Back
Top