Re: newbie; Update not working
From: Allen (mecum_at_pacbell.net)
Date: 11/21/04
- Next message: Val Mazur: "Re: Automatically populating a dataset with all the mdb's tables"
- Previous message: W.G. Ryan eMVP: "Re: Updating Multiple Tables"
- In reply to: W.G. Ryan eMVP: "Re: newbie; Update not working"
- Next in thread: Rich: "Re: newbie; Update not working"
- Reply: Rich: "Re: newbie; Update not working"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 20 Nov 2004 18:45:20 -0800
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
>> oCmd.Connection.Open()
>> oCmd.CommandText = "UPDATE Students SET AccountBalance = total WHERE
>> StudentID = iStudentID"
>> oCmd.CommandType = CommandType.Text
>> oCmd.ExecuteNonQuery()
Thanks so much for your help
Allen
"W.G. Ryan eMVP" <WilliamRyan@NoSpam.gmail.com> wrote in message
news:OwM4j62zEHA.3976@TK2MSFTNGP09.phx.gbl...
> 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
> "Allen" <mecum@pacbell.net> wrote in message
> news:enJOq$1zEHA.1264@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>>
>>
>
>
- Next message: Val Mazur: "Re: Automatically populating a dataset with all the mdb's tables"
- Previous message: W.G. Ryan eMVP: "Re: Updating Multiple Tables"
- In reply to: W.G. Ryan eMVP: "Re: newbie; Update not working"
- Next in thread: Rich: "Re: newbie; Update not working"
- Reply: Rich: "Re: newbie; Update not working"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
