Question 'Conversion from string to type double is not valid' error!

[JayWalker]

I wanna perform a function like this. When you save a record, I need the primary key of that record to be auto-generated (X001). Say, when you create a new record and save it, it should be saved like X002 automatically.
So to test this before I implement that on this project I'm working on, I created this little program.



All its supposed to do is, when you click on the OK button it shows a code that is fetched from a database table. (X001). Then when you click on it ober and over again. The number should increase. X002, X003, X004... so on.

Here's my code

Imports System.Data.SqlClient

Public Class Form1

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

        Dim strcon As String
        Dim SQLcon As SqlClient.SqlConnection

        strcon = "data source=winux; initial catalog=temperory; integrated security=SSPI; persist security info=false"
        SQLcon = New SqlClient.SqlConnection(strcon)

        Dim strsql As String
        Dim com As SqlClient.SqlCommand

        strsql = "select CONVERT(nchar, MAX(code)) as [autocode] from CodeAutoGen where form_id = '05'"
        com = New SqlClient.SqlCommand(strsql, SQLcon)

        SQLcon.Open()

        Dim myReader As SqlClient.SqlDataReader
        myReader = com.ExecuteReader

        com.Connection = SQLcon
        com.CommandText = strsql

        txtNo.Clear()
        If myReader.Read Then
            txtNo.Text = myReader("code").ToString
        End If

        'txtNo.Text = CDbl(txtNo.Text)
        'txtNo.Text = (txtNo.Text) + 1

        SQLcon.Close()

    End Sub
End Class

But when I run it, it throws this conversion error.



I know its pretty straight forward so I tried to correct it with what I know but it wasn't successful so I searched the net for solutions. And tried some of them.



But nothing worked.

I'd be very grateful if someone would help me with this. I know this is be very simple but forgive me since I'm new to VB.NET.

Thanks very much.
I.J

 

[JuggaloBrotha]

Your biggest problem is that you're trying to convert a letter to a number, which is pretty obvious that "T" isn't a number, so you'd need to strip the letter(s) (you can save it to another variable if needed) out first, then convert it to a number & increment it, then stick the letter back on.

However, I'd like to point out that you're using Sql Server (I'm assuming it's Sql Server since you're using the SqlConnection object) and Sql Server has Stored Procedures, which in this case can be extremely handy in that not only can it insert the new record when you call it, but you can have it return the identity (the PK column(s)) of the newly added record too, though you did mention that you're wanting to use this as the PK value for the next record to go into the database, which is usually a bad approach because a record could be inserted by someone else using the program and when you insert yours it'll fail because a record with that PK already exists, you'd usually get the newly insert record's PK info after it's inserted, not before.

And why in the heck are you taking the string from the text box, converting it to a double and assigning it back to the string (it gets converted to a string again when you do that) & then you concatenate the number 1 to the end of it?
txtNo.Text = CDbl(txtNo.Text)
txtNo.Text = (txtNo.Text) + 1

Makes no sense.

You should also be in the habit of using Try/Catch blocks for database, network or file system calls to, so you can catch the exception and handle it rather than the whole application falling over, dead.

 

[JayWalker]

I understand. Separate numbers from the prefix letter. I'll try it out that way.

I know. Writing an SP was my first idea. Even tried too. But my knowledge proven to be not enough. hehe.. (I will definitely get back to it again, this is just another way I thought of doing it in the meantime)

I do use Try/Catch blocks everywhere. Since this is for testing purposes, I didn't put them.

Anyway, thanks for your advice.

 

[JuggaloBrotha]

Typically you would pass the values to the Stored Proc and once the Proc has inserted the data, you simply select SCOPE_IDENTITY() and it returns the PK field's value of your new row. There's tons of examples on the web for doing that.

http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row

 

[JayWalker]

I only have a basic knowledge in SQL. Not quite familiar with SPs n stuff yet. but hey, thanks..