Select Statement SQL in VB

Question

I understand that there are other posts such as this, however I cannot find one that will work for me and Im really at the end of my tether with this, I really dont know what to do.

I have a few tables with ID columns and name columns, that are connected by Link Tables through foreign keys etc. I'm trying to enter data into the database via a GUI and to do so I'm using insert statements into the 'regular' tables, then Select statements to get the autogen IDs from the regular table to then insert into the link tables.

The code below is what I've been trying to use to do this.

Imports System.Data.SqlClient
    Public Class Test

Private cs As New SqlConnection(".....")

Private Sub btnInsertNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsertNext.Click
        Dim ContID As Integer
        Dim FName As Integer


        cs.Open()

Using command As New SqlCommand("Select FamID From Family Where Name = '" & FName & " '", cs)
            command.Parameters.AddWithValue("@FamID", ContID)
            command.ExecuteNonQuery()
        End Using

  Using command As New SqlCommand("Select DocID From Doctors Where DocName LIKE'" & AddFam.Doctor & " '", cs)
            command.Parameters.AddWithValue("@DocID", AddFam.Doctor) ''AddFam is another form I'm using to add a family member to a Doctor
            command.ExecuteNonQuery()
        End Using

cs.close()

I'm using: VB 2010 SQL server management 2008 r2

I understand its a bit muddley but any help would be greatly appreciated, and I'm sorry if this has come up before.

Are you using IDENTITY columns in your SQL Server tables in order to autogenerate the IDs? If so, then there are special variables/functions available in SQL Server to retrieve these values -- @@IDENTITY, SCOPE_IDENTITY(), etc. — David Faber, Sep 07 '12 at 14:34
Yes I'm using Identity columns and I've been trying to call them — hmmk, Sep 07 '12 at 14:45

score 2 · Answer 1 · answered Sep 07 '12 at 14:37

It's not really clear what problem you actually have, i assume that you don't know how to retrieve newly generated IDs.

Here is an self-explanatory example on how to retrieve new identity values with ADO.NET:

Using con = New SqlConnection(connectionString)
    Dim newID As Int32
    Using insertCommand = New SqlCommand("INSERT INTO Test(Value)VALUES(@Value);SELECT CAST(scope_identity() AS int)", con)
        insertCommand.Parameters.AddWithValue("@Value", "Value1")
        con.Open()
        newID = DirectCast(insertCommand.ExecuteScalar, Int32)
    End Using

    If newID <> 0 Then
        Using updateCommand = New SqlCommand("UPDATE TEST SET Value='Value1.1' WHERE idTest=@idTest", con)
            updateCommand.Parameters.AddWithValue("@idTest", newID)
            If con.State <> ConnectionState.Open Then con.Open()
            Dim updatedRecordCount = updateCommand.ExecuteNonQuery
        End Using
    End If
End Using

The 2 important parts are:

SELECT CAST(scope_identity() AS int)

which will return the new identity value

DirectCast(insertCommand.ExecuteScalar, Int32)

which will return the new Identity column value if a new row was inserted, 0 on failure.

Since you're mixing using parameters with string-concatenation: using parameters is very important because it will prevent SQL-Injection.

Thank you for replying! I've just been trying to work with the code example you've given me, however in the Updatecommand section, its saying that the newID variable cannot be converted from a varchar value to an int datatype :s is this something with the code or to do with the database? — hmmk, Sep 07 '12 at 15:26
Then your identity value is a varchar. In general it is [not recommended](http://stackoverflow.com/a/3162274/284240) to use a varchar as primary key. But to get it working you only have to cast it to the correct type, for example: `SELECT CAST(scope_identity() AS varchar(100))` (if 100 is the length) — Tim Schmelter, Sep 07 '12 at 15:39
my identity value is an int though, I've set all primary keys as Identities, with a datatype int and not to allow nulls. After I submitted the first reply, I looked in my database and saw that the only nvarchar was the Description column but its not a primary key...so im a bit confused now! — hmmk, Sep 07 '12 at 15:46

score 0 · Answer 2 · answered Sep 07 '12 at 14:37

NO! Parameterise your queries like this.

   command = New SqlCommand("Select FamID From Family Where Name = @Fname", cs) 
   command.Parameters.AddWithValue("@Fname", ContID) 
   FamID = command.ExecuteScalar()

And ideally, you should use Scope_Identity() to get the Identity from you insert statement

score 0 · Answer 3 · answered Sep 07 '12 at 14:41

You can try with this code on two queries based on @ symbol

...
Using command As New SqlCommand("Select FamID From Family Where Name = @FamID", cs)
command.Parameters.AddWithValue("@FamID", ContID)

...

Using command As New SqlCommand("Select DocID From Doctors Where DocName LIKE @DocID", cs)
command.Parameters.AddWithValue("@DocID", AddFam.Doctor) 

...

Select Statement SQL in VB

3 Answers3