how to read data from database and increment the value

[vinosha83]

hi ...i'm new ASP.net .i'm using VB.net code to develop my project..i got one problem..

i don't knw how to read a value from database and increment the value.
the below is the code..is not reading my value from database

Dim Current_num_of_RefId As Double = 1.0



sqlCommand = New SqlCommand("SELECT RefId as Expr1000 FROM [test] where field=value ")
Current_num_of_RefId = Current_num_of_RefId + 0.1

REFtxt.Text = (Current_num_of_RefId).ToString


Dim InsertSQL As String
InsertSQL = "INSERT INTO [test]("
InsertSQL &= "RefNo)"
InsertSQL &= " VALUES ('"
InsertSQL &= REFtxt.Text & "')"




Dim con As New SqlConnection(SqlConn)
Dim cmd As New SqlCommand(InsertSQL, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()

[vinosha83]

please help me

[jleone]

OK, I see a few things wrong. Where I work we used SQL stored procedures to handle all the database stuff.

So, for an INSERT, we use a stored procedure. Within that stored procedure I call another stored procedure which will return the next key value for the table I want to do my INSERT to.

Your SELECT statement does not appear to be set up correctly since it appears you are including your vb variable inside the quotations of your SELECT. You will also want to use a SqlDataReader to read the value from your SELECT.

Try the following code to start (a rough sketch) and try to build off of it:

Dim ConnectionString as String = "Whatever your connection string is"
Dim SqlCon As SqlClient.SqlConnection
Dim SqlCmd As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader
Dim RefNum as Integer

SqlCon = New SqlClient.SqlConnection(ConnectionString)
SqlCmd = New SqlClient.SqlCommand

SqlCon.Open()
SqlCmd.Connection = SqlCon

'Is RefNo of type Integer or String (Varchar) in this table?
'This is under the assumption it's type Integer
SqlCmd.CommandText = "Select max(RefNo) from test"

dr = SqlCmd.ExecuteReader

dr.Read()

RefNum = dr.GetInt32(0)

dr.Close()

'Now you have the value in RefNum so you can increment it by whatever
RefNum += 1

SqlCmd.CommandText = "INSERT INTO test(RefNo) VALUES(" & RefNum & ")"

SqlCmd.ExecuteNonQuery

SqlCmd.Dispose()
SqlCon.Close()
SqlCon.Dispose()

SqlCmd = Nothing
SqlCon = Nothing
dr = Nothing

[vinosha83]

hi sir..u want me to do select statement using stored procedure...
but i'm not familiar using stored procedure...the code u provide to me is not working sir

[vinosha83]

i got error like this :
ExecuteReader: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.

Source Error:


Line 118: ' EIAtxt2.Text = (categorydroplist.SelectedValue + idnum + 0.1).ToString
Line 119: sqlCommand = New SqlCommand("SELECT max(EnvironmentNo)FROM [EnvironmentalTest] where CategoryId='1'")
Line 120: dr = sqlCommand.ExecuteReader
Line 121: dr.Read()
Line 122:

[vinosha83]

hi i able to solve the problerm:

Private Sub Submittxt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submittxt.Click
Dim CategoryId As Integer = categorydroplist.SelectedValue

Dim dr As SqlDataReader

Dim getUserSQL As String
UserConn.Open()
getUserSQL = "SELECT max (EnvironmentNo) FROM [EnvironmentalTest] where CategoryId=" & CategoryId.ToString
Dim sqlcommand As New SqlCommand(getUserSQL, UserConn)


dr = sqlcommand.ExecuteReader

If dr.Read Then
EIAtxt2.Text = (CDbl(dr(0)) + 0.1).ToString
dr.Close()
End If

Dim InsertSQL As String

InsertSQL = "INSERT INTO [EnvironmentalTest]("
InsertSQL &= "CategoryId,UserName,EnvironmentNo)"
InsertSQL &= " VALUES ('"
InsertSQL &= categorydroplist.SelectedValue & "','"
InsertSQL &= Session("UserName") & "','"
InsertSQL &= EIAtxt2.Text & "')"



Dim con As New SqlConnection(SqlConn)
Dim cmd As New SqlCommand(InsertSQL, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
UserConn.Close()
'dr = Nothing
'ClearControl()
BindCategory()



End Sub