newbie; Update not working

[Allen]

Hey all I'm new to .net trying to update a database with a new calculated
values. I keep getting this error.
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.

Public Function Debit(ByVal iStudentID As Integer) As Boolean

Dim dsCurrent As New DataSet
Dim dBalance As Double
Dim dPotentialBalance As Double
Dim strWarning As String
Dim frmMain As New frmStateUMain
' get the account balance using the argument for the Student ID

Dim sqlda As New SqlDataAdapter
Dim oCmd As SqlClient.SqlCommand
Dim strConn As String

strConn = Have connection string that works

Create the Command Object
oCmd = New SqlClient.SqlCommand
' Assign Connection to Command Object
oCmd.Connection = New SqlConnection(strConn)
' Open the Connection
oCmd.Connection.Open()
' Assign the SQL to the Command Object
oCmd.CommandText = "[GetAccountBalance]"
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("@ID", System.Data.SqlDbType.Int, 0)
oCmd.Parameters("@ID").Value = iStudentID
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
dBalance = bal

MessageBox.Show("SQL statement succeeded", _
dBalance)

'dsCurrent.Clear()
' Close the Connection
oCmd.Connection.Close()
Catch oExcept As Exception
dBalance = 0
MessageBox.Show("Error executing SQL: " & _
oExcept.Message, "btnExecute_Click()")
End Try

' this method will add the book price amount
' to the student's account balance.

Dim total As Double
total = dBalance + frmMain.cellValue
oCmd.Connection.Open()
oCmd.CommandText = "UPDATE Students SET AccountBalance = total WHERE
StudentID = iStudentID"
oCmd.CommandType = CommandType.Text
oCmd.ExecuteNonQuery()
RecordPurchase(iStudentID)

' commit the changes
' return success

Debit = True

 

[W.G. Ryan eMVP]

Allen:

Lots of stuff could be going wrong. First, I'd try/catch the ExecuteScalar
line ie

try
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
catch (ex as SqlException)
Debug.Assert(false, ex.ToString());
End Try

Next, If this is SqlServer, remove the [] - I'm not 100% positive that it's
the problem but I'm pretty sure that it is.

The assertion should fail if the ExecuteScalar is the problme and the
exception message should give you some information.

The more glaring thing is in the second command though. You are using an
absolute string so those values in the where clause are being used as
literals - the param values aren't being substituted.. Your command
parameters collection still has a value in it @ID which is getting passed to
the second command - and the second command isn't accepting @ID as aa
parameter - no doubt causing A problem even if it's not this problem.

Also, try/catch/finally the connection. If your command fails - then that
connection is probably going to leak - definitely bad news.
--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Hey all I'm new to .net trying to update a database with a new calculated
values. I keep getting this error.
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.

Public Function Debit(ByVal iStudentID As Integer) As Boolean

Dim dsCurrent As New DataSet
Dim dBalance As Double
Dim dPotentialBalance As Double
Dim strWarning As String
Dim frmMain As New frmStateUMain
' get the account balance using the argument for the Student ID

Dim sqlda As New SqlDataAdapter
Dim oCmd As SqlClient.SqlCommand
Dim strConn As String

strConn = Have connection string that works

Create the Command Object
oCmd = New SqlClient.SqlCommand
' Assign Connection to Command Object
oCmd.Connection = New SqlConnection(strConn)
' Open the Connection
oCmd.Connection.Open()
' Assign the SQL to the Command Object
oCmd.CommandText = "[GetAccountBalance]"
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("@ID", System.Data.SqlDbType.Int, 0)
oCmd.Parameters("@ID").Value = iStudentID
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
dBalance = bal

MessageBox.Show("SQL statement succeeded", _
dBalance)

'dsCurrent.Clear()
' Close the Connection
oCmd.Connection.Close()
Catch oExcept As Exception
dBalance = 0
MessageBox.Show("Error executing SQL: " & _
oExcept.Message, "btnExecute_Click()")
End Try

' this method will add the book price amount
' to the student's account balance.

Dim total As Double
total = dBalance + frmMain.cellValue
oCmd.Connection.Open()
oCmd.CommandText = "UPDATE Students SET AccountBalance = total WHERE
StudentID = iStudentID"
oCmd.CommandType = CommandType.Text
oCmd.ExecuteNonQuery()
RecordPurchase(iStudentID)

' commit the changes
' return success

Debit = True

 

[Allen]

W.G. Thanks for the info.
The first command is working ok I get the accountbalance and am able to add
them to the total but I need to update the AccountBalance's new total back
to the database in the second command. what is the bestway to update a table
in a sql database. I did take out the @ID's that I had in the second
command. Do I need to rename each connection or is just closing and
repoening it enough? The table name is "Students" and the field names are
StudentID, FName, LName, MajorID, AccountBalance, BookStoreEmp. I thought
all I had to do was run a query to update it but it seems to need more than
that? Is there more than just opening the connection and running the Sql
statement. I have done a lot of Access dev but .net seems to be pickie. I
have looked on MSDN and other sites but cant seem to find the right stuff to
help me out with this. Also why is there not just a Execute for a sql
statement?
Do I need to import somthing more than these?
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.Common


Thanks so much for your help
Allen

Allen:

Lots of stuff could be going wrong. First, I'd try/catch the
ExecuteScalar
line ie

try
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
catch (ex as SqlException)
Debug.Assert(false, ex.ToString());
End Try

Next, If this is SqlServer, remove the [] - I'm not 100% positive that
it's
the problem but I'm pretty sure that it is.

The assertion should fail if the ExecuteScalar is the problme and the
exception message should give you some information.

The more glaring thing is in the second command though. You are using an
absolute string so those values in the where clause are being used as
literals - the param values aren't being substituted.. Your command
parameters collection still has a value in it @ID which is getting passed
to
the second command - and the second command isn't accepting @ID as aa
parameter - no doubt causing A problem even if it's not this problem.

Also, try/catch/finally the connection. If your command fails - then that
connection is probably going to leak - definitely bad news.
--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Hey all I'm new to .net trying to update a database with a new calculated
values. I keep getting this error.
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.

Public Function Debit(ByVal iStudentID As Integer) As Boolean

Dim dsCurrent As New DataSet
Dim dBalance As Double
Dim dPotentialBalance As Double
Dim strWarning As String
Dim frmMain As New frmStateUMain
' get the account balance using the argument for the Student ID

Dim sqlda As New SqlDataAdapter
Dim oCmd As SqlClient.SqlCommand
Dim strConn As String

strConn = Have connection string that works

Create the Command Object
oCmd = New SqlClient.SqlCommand
' Assign Connection to Command Object
oCmd.Connection = New SqlConnection(strConn)
' Open the Connection
oCmd.Connection.Open()
' Assign the SQL to the Command Object
oCmd.CommandText = "[GetAccountBalance]"
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("@ID", System.Data.SqlDbType.Int, 0)
oCmd.Parameters("@ID").Value = iStudentID
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
dBalance = bal

MessageBox.Show("SQL statement succeeded", _
dBalance)

'dsCurrent.Clear()
' Close the Connection
oCmd.Connection.Close()
Catch oExcept As Exception
dBalance = 0
MessageBox.Show("Error executing SQL: " & _
oExcept.Message, "btnExecute_Click()")
End Try

' this method will add the book price amount
' to the student's account balance.

Dim total As Double
total = dBalance + frmMain.cellValue
oCmd.Connection.Open()
oCmd.CommandText = "UPDATE Students SET AccountBalance = total WHERE
StudentID = iStudentID"
oCmd.CommandType = CommandType.Text
oCmd.ExecuteNonQuery()
RecordPurchase(iStudentID)

' commit the changes
' return success

Debit = True

 

[Guest]

If you were missing Imports statements, your code wouldn't compile at all.
Actually, you never really need to import anyhing (at least not that I can
think of). Imports statements are really just a shorthand-type of thing for
the compiler. For example, if you say, "Imports System.Data.SQLCLient", then
the namespace System.Data.SQLCLient becomes is implicit in Declarations, like
"Dim oCmd As SqlClient.SqlCommand". If you weren't using the Imports
statement, you could code: "Dim oCmd as System.Data.SQLCLient.SqlCommand" and
get the same effect.

I think your problem has to do with your command object and its parameters.
Before I go any further, let me make this suggestion: I would use two
separate command objects: One to execute the stored procedure to get the
original account balance, and a totally separate comand object to take care
of the updates. What your doing is probably fine, EXCEPT, that after you
take care of a few other things, the parameters collection you'll use in the
second command won't work for the first command. Plus, the CommandText and
CommandType properties have to be different, anyway.

If your application needs to repeatedly get different student account and
then update the balance (wich would be quite typical), I would definitely
recommend setting up two separate command objects at initialization time -
complete with the parameters they will need - and then just seting parameter
values and executing each command as needed.

You can use the same connecton for both commands. Whether you close the
connection and reopen it between executing the Get command and the Update
command depends on what has to happen in between. It looks like you are
getting the price of a book from a form, in which case you'll be waiting for
the user to enter data. In such a case, you definitely want to close the
connection after the GET and reopen it for the UPDATE.

Now, lets look at that UPDATE command. Your command text needs to look
something like this:

"UPDATE Students SET AccountBalance = @total WHERE
StudentID = @StudentID"

This command needs to have two parameters in its parameter collection. The
first one will be for the total amount that will be stored back to the
database and the second one will be for the StudentID. There's different
ways to do it, but you need to make sure that the parameter @total has the
new account balance in its value property and that @StudentID has the correct
value as well when you issue ExecuteNonQuery.

Remeber that CommandText is just a string value....it doesn't magically
morph variable names like "total" into their values. Having said that, you
could do something like this:

oCmd.CommandText = "UPDATE Students SET AccountBalance = " & total.tostring
& StudentID = " & iStudentID.tostring.

If this is going to be your command string, you wouldn't need any parameters.

HTH

W.G. Thanks for the info.
The first command is working ok I get the accountbalance and am able to add
them to the total but I need to update the AccountBalance's new total back
to the database in the second command. what is the bestway to update a table
in a sql database. I did take out the @ID's that I had in the second
command. Do I need to rename each connection or is just closing and
repoening it enough? The table name is "Students" and the field names are
StudentID, FName, LName, MajorID, AccountBalance, BookStoreEmp. I thought
all I had to do was run a query to update it but it seems to need more than
that? Is there more than just opening the connection and running the Sql
statement. I have done a lot of Access dev but .net seems to be pickie. I
have looked on MSDN and other sites but cant seem to find the right stuff to
help me out with this. Also why is there not just a Execute for a sql
statement?
Do I need to import somthing more than these?
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.Common


Thanks so much for your help
Allen

Allen:

Lots of stuff could be going wrong. First, I'd try/catch the
ExecuteScalar
line ie

try
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
catch (ex as SqlException)
Debug.Assert(false, ex.ToString());
End Try

Next, If this is SqlServer, remove the [] - I'm not 100% positive that
it's
the problem but I'm pretty sure that it is.

The assertion should fail if the ExecuteScalar is the problme and the
exception message should give you some information.

The more glaring thing is in the second command though. You are using an
absolute string so those values in the where clause are being used as
literals - the param values aren't being substituted.. Your command
parameters collection still has a value in it @ID which is getting passed
to
the second command - and the second command isn't accepting @ID as aa
parameter - no doubt causing A problem even if it's not this problem.

Also, try/catch/finally the connection. If your command fails - then that
connection is probably going to leak - definitely bad news.
--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Hey all I'm new to .net trying to update a database with a new calculated
values. I keep getting this error.
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.

Public Function Debit(ByVal iStudentID As Integer) As Boolean

Dim dsCurrent As New DataSet
Dim dBalance As Double
Dim dPotentialBalance As Double
Dim strWarning As String
Dim frmMain As New frmStateUMain
' get the account balance using the argument for the Student ID

Dim sqlda As New SqlDataAdapter
Dim oCmd As SqlClient.SqlCommand
Dim strConn As String

strConn = Have connection string that works

Create the Command Object
oCmd = New SqlClient.SqlCommand
' Assign Connection to Command Object
oCmd.Connection = New SqlConnection(strConn)
' Open the Connection
oCmd.Connection.Open()
' Assign the SQL to the Command Object
oCmd.CommandText = "[GetAccountBalance]"
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("@ID", System.Data.SqlDbType.Int, 0)
oCmd.Parameters("@ID").Value = iStudentID
Dim bal As Double = CDbl(oCmd.ExecuteScalar())
dBalance = bal

MessageBox.Show("SQL statement succeeded", _
dBalance)

'dsCurrent.Clear()
' Close the Connection
oCmd.Connection.Close()
Catch oExcept As Exception
dBalance = 0
MessageBox.Show("Error executing SQL: " & _
oExcept.Message, "btnExecute_Click()")
End Try

' this method will add the book price amount
' to the student's account balance.

Dim total As Double
total = dBalance + frmMain.cellValue
oCmd.Connection.Open()
oCmd.CommandText = "UPDATE Students SET AccountBalance = total WHERE
StudentID = iStudentID"
oCmd.CommandType = CommandType.Text
oCmd.ExecuteNonQuery()
RecordPurchase(iStudentID)

' commit the changes
' return success

Debit = True