No data in my DB after running a Insert SPROC

  • Thread starter Thread starter Angelina
  • Start date Start date
A

Angelina

I have written this code for an onclick event of a button
so that i can run a stored procedure i created in access.
The SPROC performs an insert of 2 values into a table.
here is my code:

Private Sub Button1_Click(ByVal sender As
System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim Cn As New SqlConnection("initial
catalog=TestCaraDBmsde;integrated
security=SSPI;persist
security info=False;workstation id=USER;packet
size=4096")
Dim cmd As New SqlCommand("SPInsMeal", Cn)
cmd.CommandType =
CommandType.StoredProcedure

cmd.Parameters.Add( _
New SqlParameter("@Meal_desc",
SqlDbType.Char, 50)).Value = txtMealdesc.Text
cmd.Parameters.Add(New SqlParameter("@Cost",
SqlDbType.Money))

Try
Cn.Open()
cmd.ExecuteNonQuery()
Catch ex as Exception
ex.tostring()
Finally
Cn.Close()
End Try

When i check the database for the inserted values, they
are not there. Is there something that im not doing
correctly or missing out?
 
One suggestion would be to modify your catch block
to "Throw" the exception instead of just make it a
string. It could be that you are getting an error and it
is not being "raised".

You could also dim a variable as int16 and set it equal to
cmd.ExecuteNonQuery, that will tell you the number of rows
affected by your command.

Hope this helps.

Bart A. Robinson, MCP
 
Hi,
thx for your input.
I just wanted to know what code i would write to 'throw'
the exception?? Its the first time im using this
statement!!

also where do i declare the varible:
Dim RowAffect as int16 as cmd.ExecuteNonQuery
Is this correct?
 
Your code should look something like this:

Dim RowAffect as int16
Dim Cn As New SqlConnection("initial
catalog=TestCaraDBmsde;integrated
security=SSPI;persist
security info=False;workstation id=USER;packet
size=4096")
Dim cmd As New SqlCommand("SPInsMeal", Cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( _
New SqlParameter("@Meal_desc", _
SqlDbType.Char, 50)).Value = txtMealdesc.Text
cmd.Parameters.Add(New SqlParameter("@Cost",
SqlDbType.Money))

Try
Cn.Open()
RowAffect = cmd.ExecuteNonQuery()

'here you could check to see what the value

Catch ex as Exception
Throw ex
Finally
cn.Close()
End Try


That is how you would "Throw" an exception. Looking at
your code though, if this procedure is for a button you
probably just want to message box the exception to alert
the end user.

MsgBox(ex.Message)

If you were writing this function in a component that is
called by your user interface, then you would want to
throw the exception up to the user interface for
displaying, but in your case you can simply message box
the error.

You can even setup a condition that states if
the "RowAffect" is not greater than 1 then display a
custom exception error message.

Hope this helps.

Bart A. Robinson, MCP
 
hi,

i got an error message after running my code. i have
tried to search around for the solution but could not
find out what iv done wrong. can anyone make sense of
this error?...

An unhandled exception of
type 'System.Data.SqlClient.SqlException' occurred in
testComboBox.exe

Additional information: System error.

The application stops and a yellow arrow appears next to
the 'Finally' statment in the try-catch

heres my full code:

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click

Dim RowAffect As Int16
Dim Cn As New SqlConnection("initial
catalog=TestCaraDBmsde;integrated
security=SSPI;persist security
info=False;workstation id=USER;packet
size=4096")
Dim cmd As New SqlCommand("SPInsMeal", Cn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( _
New SqlParameter("@Meal_desc",
SqlDbType.Char, 50)).Value = txtMealdesc.Text
cmd.Parameters.Add(New SqlParameter("@Cost",
SqlDbType.Money)).Value = txtCost.Text

Try
Cn.Open()
RowAffect = cmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
Cn.Close()
End Try

Can anyone help me resolve this? I have followed the
books and dont know what is wrong with my version!!!
thx
 
Try the following:

'declare the parameters
cmd.Parameters.Add("@Meal_desc",SqlDbType.Char, 50)
cmd.Parameters.Add("@Cost",SqlDbType.Money)
'now set the values
cmd.Parameters("@Meal_desc").Value = txtMealdesc.Text
cmd.Parameters("@Cost").Value = txtCost.Text

Try
Cn.Open()
RowAffect = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
Finally
Cn.Close()
End Try

Hope this helps.

Bart A. Robinson, MCP
 
Back
Top