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

JayWalker

Member
Joined
Jun 20, 2011
Messages
15
Programming Experience
Beginner
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.

form.png

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


VB.NET:
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.

exception error.png

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.

convert error 1.png

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

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
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

Member
Joined
Jun 20, 2011
Messages
15
Programming Experience
Beginner
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

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
Top Bottom