Question Outputting Data in Msg Box, 'Conversion from type 'DBNull' to type 'String' error

[Manny123]

Dim UserName As String
Dim Passwordt As String
Dim SecurityQ As String
Dim SecurityAns As String
Passwordt = txtpass.Text
SecurityQ = CboSecurityQs.Text
SecurityAns = txtSecurityans.Text
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database genk.mdb;")

str = "INSERT INTO Users (FirstName, LastName, Username, Pword, Security_question, Security_answer ) values ('" & Firstname & "','" & Lastname & "','" & UserName & "','" & Passwordt & "','" & SecurityQ & "','" & SecurityAns & "')"
cmd = New OleDbCommand(str, cn)
Try
mes = cmd.ExecuteNonQuery
Catch ex As Exception
MsgBox(ex.ToString)
End Try
MessageBox.Show("User Added")

Dim value As String
Dim com2 As OleDbCommand = New OleDbCommand
com2.CommandText = "SELECT max(UserID) from Users where Username = '" & UserName & "'"
com2.Connection = cn
value = CType(com2.ExecuteScalar(), String)
cn.Close()
MsgBox("Your UserID is " & value, MsgBoxStyle.Information, "UserID")


Bascially i'm trying to output the userId of the client once they input their details and clicked the confrim button. I'm trying to execute the str string which will retrieve the userId from the client and pass it into the value variable to output it how ever i@m getting an error message of 'Conversion from type 'DBNull' to type 'String' is not valid' at : value = CType(com2.ExecuteScalar(), String) :s


could I get some help please
Thanks

 

[jmcilhinney]

First things first, don't ever use string concatenation to insert values into SQL code. As just one example of why, your application will throw an exception if the user's last name is O'Connor. To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

Secondly, it doesn't appear that you are preventing users creating duplicate user names. What kind of system would allow multiple users to have the same user name? If you have unique user names then you don't need the MAX function call in the second query because you know that there will only be one match.

Now, looking at your code, if the insertion fails you show the error message but you then also show a success message and go on to try to get the user ID. Surely if there's an exception that should be the end of it because the record wasn't inserted. Is there an exception in this case? If so then that's your issue.

Assuming that there is no exception and the record is inserted then I can only assume that you are not using an AutoNumber column for the user ID. You aren't inserting the ID so you need the database to generate it for you, in which case you need an AutoNumber column.

 

[hipturtle]

Hello

I agree completely with jmcilhinney about your code. One other thing, about the null value. If you are trying to retrieve a null into a string VB.Net can't cope with it, so you need to do a check before you do the conversion.

Example:

IF Not TestValue is Nothing then
'The TestValue is not Null..
Value = TestValue
Else
'The TestValue is Null..
'Whatever code you want to run in the case of a null value
End IF

 

[Manny123]

First things first, don't ever use string concatenation to insert values into SQL code. As just one example of why, your application will throw an exception if the user's last name is O'Connor. To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

Secondly, it doesn't appear that you are preventing users creating duplicate user names. What kind of system would allow multiple users to have the same user name? If you have unique user names then you don't need the MAX function call in the second query because you know that there will only be one match.

Now, looking at your code, if the insertion fails you show the error message but you then also show a success message and go on to try to get the user ID. Surely if there's an exception that should be the end of it because the record wasn't inserted. Is there an exception in this case? If so then that's your issue.

Assuming that there is no exception and the record is inserted then I can only assume that you are not using an AutoNumber column for the user ID. You aren't inserting the ID so you need the database to generate it for you, in which case you need an AutoNumber column.

Yes my userID cloumn in my database is set to autonumber because they do not actually input the userID in themselves

I tried removing the Max function however when i run it and try to add a user, i get an output message saying "User added" however when i check my database nothing is there :s
When i put the max function back in i get the 'Conversion from type 'DBNull' to type 'String' error back again

 

[jmcilhinney]

What value does ExecuteNonQuery return.

 

[Manny123]

The Insert statement with the values inputted by the user i.e. first name, last name, password etc

 

[jmcilhinney]

No that is not what it returns. That is what you pass to it as an argument. I'm asking what value it returns, i.e. what value does it pass back to you after executing. Here's a clue:

[B][U]mes[/U][/B] = cmd.ExecuteNonQuery