VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
Go Back  Xtreme Visual Basic Talk > > > VS 2010 : Pulling next number from SQL


Reply
 
Thread Tools Display Modes
  #1  
Old 09-16-2013, 11:41 AM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default VS 2010 : Pulling next number from SQL


I am new to the Visual Basic programming and have been teaching myself how to do it using various tutorials and books. What I am trying to do currently is look in a Colum of an SQL table (Table Name : TT_Trouble_Ticket, Column Name: TT_ID) and pull the next available number and write it to the table. Below is the code I have written so far:

Private Sub SubBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubBTN.Click
' create a new SqlConnection object with the appropriate connection string
Dim sqlConn As New SqlConnection(CONN_STRING)
TTDataAdapter = New SqlDataAdapter("select * from TT_Trouble_Ticket", sqlConn)
'OleDbConnection i
' open the connection
sqlConn.Open()
'// do some operations here...
Dim TTDataSet As New DataSet()
TTDataAdapter.FillSchema(TTDataSet, SchemaType.Source, "TT_Trouble_Ticket")
Dim table As DataTable = TTDataSet.Tables("TT_Trouble_Ticket")
Dim newRecord As DataRow = table.NewRow()
newRecord("TT_Req") = TTReqBy.Text
newRecord("TT_Email") = TTEmail.Text
newRecord("TT_Dept") = TTDep.Text
newRecord("TT_Problem") = TTProb.Text
newRecord("TT_Sys_Dwn") = TTSysDwn.Checked
newRecord("TT_Email_Error") = TTEmailp.Checked
newRecord("TT_Intuitive_Error") = TTInt.Checked
newRecord("TT_SMS_Error") = TTSMS.Checked
newRecord("TT_Date") = TTDate.Text

table.Rows.Add(newRecord)
Dim command As New SqlCommandBuilder(TTDataAdapter)
TTDataAdapter.Update(TTDataSet, "TT_Trouble_Ticket")
TTDataAdapter.Dispose()
'// close the connection
sqlConn.Close()

Me.Close()
MainForm.Show()
End Sub

What I want to do is have the code look in the table at Column TT_ID and pull the next available number and then write it back with all of the other records, store it as a string, and also print it out in a massage box for the person filling out the form to see.
Reply With Quote
  #2  
Old 09-17-2013, 03:39 AM
DrPunk's Avatar
DrPunkVS 2010 : Pulling next number from SQL DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

You are probably better off using an Identity column in the database, because that does all this work for you.

And it won't run into a situation where your program could end up with 2 records having the same ID (although you could use rules on the database to stop that happening but that just means you have to do a load more work when errors happen). If two people were to add records at the same time they could get the same result from the ID query below.

But if you REALLY don't want to use an Identity column, you'd typically run a MAX query on the field you're interested in and add 1 to it. Either in the query or in your program.

Code:
dim cmd as new SQLCommand("SELECT COALESCE(MAX(TT_ID), 0) FROM TT_Trouble_Ticket", sqlConn)

dim NextID as integer
NextID = cmd.executeScalar + 1

' Then you're free to use NextID
The idea of having the COALESCE in the query is that MAX(TT_ID) will return Null if the table is empty. The COALESCE means that if the result (of MAX(TT_ID)) is Null it will return 0 instead of Null.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 09-17-2013, 08:45 AM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default

If I were to use the Identity column how would I return the number so the person entering the record could see it? also I want to store that number as a string so that I can use it in an email later in the code.
Reply With Quote
  #4  
Old 09-17-2013, 09:25 AM
DrPunk's Avatar
DrPunkVS 2010 : Pulling next number from SQL DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

You'd do that using an output parameter in the query. You'd tend to use output parameters with stored procedures, but you can still use them in text commands.

It uses the SCOPE_IDENTITY call of SQL Server to get the last identity that was added to the table, and passes that back to the Command via a Parameter.

For simplicities sake, this INSERT command only updates one field in the database. The important bit of the query is the semi colon and what comes after it.
Code:
Dim cn As New SqlConnection(CONN_STRING)

Dim cmd As New SqlCommand("INSERT INTO TT_Trouble_Ticket (TT_Req) VALUES ('" & TTReqBy.Text & "'); SET @id = SCOPE_IDENTITY()", cn)

' Create the parameter that's in the query
Dim prm As New SqlParameter() 
prm.ParameterName = "@id"
prm.Direction = ParameterDirection.Output
prm.SqlDbType = SqlDbType.Int

' Add the parameter to the command object
cmd.Parameters.Add(prm)

Try
    cn.Open()
    
    ' Execute the insert command
    cmd.ExecuteNonQuery()

    ' Show that the @id parameter that was added has the Identity of the record added.
    ' You could store this value instead and use it wherever.
    MessageBox.Show(cmd.Parameters("@id").Value)

Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    cn.Close()
End Try
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 09-17-2013, 01:50 PM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default

When I dropped in your code this is what I am getting

"Conversion type from 'DBNull' to type 'String' is not valid
Reply With Quote
  #6  
Old 09-17-2013, 01:53 PM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default

Here is how I incorporated it

vb.net Code:
Private Sub SubBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubBTN.Click ' create a new SqlConnection object with the appropriate connection string Dim sqlConn As New SqlConnection(CONN_STRING) TTDataAdapter = New SqlDataAdapter("select * from TT_Trouble_Ticket", sqlConn) 'OleDbConnection i ' open the connection sqlConn.Open() Dim cmd As New SqlCommand("SELECT COALESCE(MAX(TT_ID), 0) FROM TT_Trouble_Ticket", sqlConn) Dim NextID As Integer NextID = cmd.ExecuteScalar + 1 ' Then you're free to use NextID '// do some operations here... Dim TTDataSet As New DataSet() TTDataAdapter.FillSchema(TTDataSet, SchemaType.Source, "TT_Trouble_Ticket") Dim table As DataTable = TTDataSet.Tables("TT_Trouble_Ticket") Dim newRecord As DataRow = table.NewRow() newRecord("TT_Req") = TTReqBy.Text newRecord("TT_Email") = TTEmail.Text newRecord("TT_Dept") = TTDep.Text newRecord("TT_Problem") = TTProb.Text newRecord("TT_Sys_Dwn") = TTSysDwn.Checked newRecord("TT_Email_Error") = TTEmailp.Checked newRecord("TT_Intuitive_Error") = TTInt.Checked newRecord("TT_SMS_Error") = TTSMS.Checked newRecord("TT_Date") = TTDate.Text newRecord("TT_ID") = NextID table.Rows.Add(newRecord) Dim command As New SqlCommandBuilder(TTDataAdapter) TTDataAdapter.Update(TTDataSet, "TT_Trouble_Ticket") CreateMsg() createSMTP() sendMessage() TTDataAdapter.Dispose() '// close the connection sqlConn.Close() Dim cmd2 As New SqlCommand("INSERT INTO TT_Trouble_Ticket (TT_Req) VALUES ('" & TTReqBy.Text & "'); SET @id = SCOPE_IDENTITY()", sqlConn) ' Create the parameter that's in the query Dim prm As New SqlParameter() prm.ParameterName = "@id" prm.Direction = ParameterDirection.Output prm.SqlDbType = SqlDbType.Int ' Add the parameter to the command object cmd.Parameters.Add(prm) Try sqlConn.Open() ' Execute the insert command cmd.ExecuteNonQuery() ' Show that the @id parameter that was added has the Identity of the record added. ' You could store this value instead and use it wherever. MessageBox.Show(cmd.Parameters("@id").Value) Catch ex As Exception MessageBox.Show(ex.Message) Finally sqlConn.Close() End Try 'MessageBox.Show("Your Trouble Ticket ID is " & NextID) Me.Close() MainForm.Show() End Sub
Reply With Quote
  #7  
Old 09-19-2013, 03:15 AM
DrPunk's Avatar
DrPunkVS 2010 : Pulling next number from SQL DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by rwj_2006 View Post
When I dropped in your code this is what I am getting

"Conversion type from 'DBNull' to type 'String' is not valid
My guess is it's erroring on...
Code:
MessageBox.Show(cmd.Parameters("@id").Value)
Not that I should have to guess.

If it is then I'd guess there's no Identity column set in the database Table so SCOPE_IDENTITY is Null, therefore @id would be Null and it can't convert that Null to a string to put into a messagebox.

SET @id = SCOPE_IDENTITY requires there to be an Identity column set in the Table being added to.
__________________
There are no computers in heaven!
Reply With Quote
  #8  
Old 09-19-2013, 09:48 AM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default

Does it make a difference that I am using SQL Server 2005?
Reply With Quote
  #9  
Old 09-19-2013, 10:10 AM
DrPunk's Avatar
DrPunkVS 2010 : Pulling next number from SQL DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

No. Version shouldn't matter, but if your table did not have an automatic Identity column then you need to give it one for this to work.

In the table design (of Microsoft SQL Server Management Studio), in the column properties page there is an Identity Specification section. Extend that out, clicking the +, and you can change the "(Is Identity)" from No to Yes. The column must be numeric. It gets disabled for other types.

With that column set to Is Identity it will auto increment that field based on the Identity Increment and Identity Seed properties of that column (most commonly 1, 1). And that Identity should be returned by SCOPE_IDENTITY() when you add a record to the table.
__________________
There are no computers in heaven!

Last edited by DrPunk; 09-19-2013 at 10:26 AM.
Reply With Quote
  #10  
Old 09-19-2013, 12:11 PM
rwj_2006 rwj_2006 is offline
Newcomer
 
Join Date: Sep 2013
Posts: 6
Default

I fixed it in the code you gave me I forgot to change all the cmd to cmd2. Now I am getting

must declare the scalar variable @id
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
 
VS 2010 : Pulling next number from SQL
VS 2010 : Pulling next number from SQL
 
-->