SQLException

[hubbard92]

Hi,

I'm trying to write my first vb.net program to update a date/time field in a SQL database. I'm trying to update the table by using passing a date parameter to a stored procedue and then run the stored procedure to update my table. I'm receiving the following error message:

An Unhandled exception of type 'System.Data.SqlClient.SQLException occured in System.data.dll'

First, my stored procedure is as follows:

CREATE PROCEDURE [dbo].[UpdateProcRunTracker] (
 
@ProcessDate as datetime)
AS
UPDATE Run_Tracker SET
Process_date = @Processdate
GO

Next, my vb.net code to run the procedure:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmmRunTracker [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ws_date2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2] = DateTimePicker1.Value[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] parameter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlParameter[/SIZE]
 
[SIZE=2]cnnRunTracker = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection[/SIZE]
[SIZE=2]cnnRunTracker.ConnectionString = "server=xxxxxxxx;database=" + List1.SelectedItem + ";UID=xxxxxxxx;password=xxxxxxxx;"[/SIZE]
 
[SIZE=2][COLOR=#008000]'Open the conncection[/COLOR][/SIZE]
[SIZE=2]cnnRunTracker.Open()[/SIZE]
 
[SIZE=2]cmmStoredProcedure = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand("UpdateProcRunTracker", cnnRunTracker)[/SIZE]
[SIZE=2]cmmStoredProcedure.CommandType = CommandType.StoredProcedure[/SIZE]
[SIZE=2]' set parameter[/SIZE]
[SIZE=2]Parameter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlParameter("@process_date", SqlDbType.DateTime, 8)[/SIZE]
[SIZE=2]parameter.Value = ws_date2 [/SIZE][SIZE=2][COLOR=#008000]'Value of the parameter[/COLOR][/SIZE]
[SIZE=2]Parameter.Direction = ParameterDirection.Output[/SIZE]
 
[SIZE=2]cmmStoredProcedure.Parameters.Add(Parameter)[/SIZE]
[SIZE=2]cmmStoredProcedure.ExecuteNonQuery()[/SIZE]
[SIZE=2]cnnRunTracker.Close()[/SIZE]

Any help would be greatly appreciated.

 

[lingsn]

I think trying to change the code

Parameter.Direction = ParameterDirection.Output

to

Parameter.Direction = ParameterDirection.Input

If the error still occur, try to use 'Try-Catch' to get more information on the error occur.

Try
 
Catch ex as Exception
   Messagebox.show(ex.toString())
End Try

 

[hubbard92]

Hi again to anyone reading this post:

I found in some documentation about adding some logic to catch the error and give a little more detail on what the error is.

The error I was getting is becasue my parameter in my code did not match my parameter in my stored Procedure. Opps...

By the way, anyone reading this and was getting the same error or something similar, the code I added was as follows in RED:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmmRunTracker [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ws_date2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2] = DateTimePicker1.Value[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] parameter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlParameter[/SIZE]

[SIZE=2][SIZE=2][COLOR=#0000ff][COLOR=red]On[/COLOR][/COLOR][/SIZE][COLOR=red][SIZE=2]Error[/SIZE][SIZE=2]GoTo[/SIZE][SIZE=2] Err_enableUnstructuredExceptionHandling[/SIZE][/COLOR]
[/SIZE]
[SIZE=2]cnnRunTracker = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection[/SIZE]
[SIZE=2]cnnRunTracker.ConnectionString = "server=xxxxxxxx;database=" + List1.SelectedItem + ";UID=xxxxxxxx;password=xxxxxxxx;"[/SIZE]
 
[SIZE=2][COLOR=#008000]'Open the conncection[/COLOR][/SIZE]
[SIZE=2]cnnRunTracker.Open()[/SIZE]
 
[SIZE=2]cmmStoredProcedure = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand("UpdateProcRunTracker", cnnRunTracker)[/SIZE]
[SIZE=2]cmmStoredProcedure.CommandType = CommandType.StoredProcedure[/SIZE]
 
[SIZE=2]Parameter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlParameter("@processdate", SqlDbType.DateTime, 8)[/SIZE]
[SIZE=2]parameter.Value = ws_date2 [/SIZE][SIZE=2][COLOR=#008000]'Value of the parameter[/COLOR][/SIZE]
[SIZE=2]Parameter.Direction = ParameterDirection.Output[/SIZE]
 
[SIZE=2]cmmStoredProcedure.Parameters.Add(Parameter)[/SIZE]
[SIZE=2]cmmStoredProcedure.ExecuteNonQuery()[/SIZE]
[SIZE=2]cnnRunTracker.Close()[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=red]Err_enableUnstructuredExceptionHandling:[/COLOR][/SIZE]
[SIZE=2][COLOR=red]MsgBox(Err.Description & vbCrLf & Err.Erl & vbCrLf & Err.Number & vbCrLf & Err.Source)[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/SIZE]

Now that I have that fixed, I'm now receiving the following error:

"Formal parameter @ProcessDate was defined as Output but the actual parameter not declared output."

Not quite sure what this is referring to:

Thanks again for anyone who may be able to shed a little light on the subject.

 

[hubbard92]

Thanks, changing output to input did the trick. Thanks so much.

 

[cjard]

ps,, "On Error" really really really isnt the way we do error handling any more. You can have a look at the cool ways to handle errors by reading up on Try/Catch error handling.


Your original code has an additional problem that you didnt mention, and would probably have caused a problem too:

CREATE PROCEDURE [dbo].[UpdateProcRunTracker] (
 
[B][SIZE=4]@ProcessDate as datetime[/SIZE][/B])
AS
UPDATE Run_Tracker SET
Process_date = @Processdate
GO
 
cmmStoredProcedure.CommandType = CommandType.StoredProcedure
[SIZE=2]' set parameter[/SIZE]
[SIZE=2]Parameter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlParameter[B][SIZE=4]("@process_date", SqlDbType.DateTime[/SIZE][/B], 8)[/SIZE]
[SIZE=2]parameter.Value = ws_date2 [/SIZE][SIZE=2][COLOR=#008000]'Value of the parameter[/COLOR][/SIZE]
[SIZE=2]Parameter.Direction = ParameterDirection.Output[/SIZE]

Giving the parameters a different name in code, to what it is in the DB might mean that SQL server couldnt associate the vb parameter with the stored procedure argument.. Keep your names the same and this shouldnt be an issue.

 

[TechGnome]

Expanding upon cjard's comment about proper error handling... on error needs to be ripped out of the language.

here's an example of the "proper .NET" way:

Try
    'Do SQL Stuff Here
Catch sqlEx as SQLException
   Messagebox.Show (sqlEx.ToString)
Catch ex As Exception
   Messagebox.Show (ex.ToString)
End Try

The idea is to catch more specific errors first, then get more generalized.

-tg

 

[cjard]

Thanks TG!

Other rules to follow:

Dont do either of these:

Try
  'dangerous code
Catch ex as Exception
 
End Try

You should never catch an error then do nothing with it/hide it

Try
  'dangerous code
Catch ex as FileNotFoundException
  Throw New FileNotFoundException(ex.Message)
End Try

You catch it and then make a new exception (witha different stack trace by the way, making debugging harder) of the same thing, and throw it on.. You shouldnt put the Try/Catch in at all in that situation