Input string was not in a correct format.

[eulogy6]

I get an error "Input string was not in a correct format"
and I don't understand why?
I'm going to use the following code in order to change the user's password with the "newtxt1.Text", having an Access database with 1 table(Users) with UserID|Username|Password.

Can anyone tell me what I'm doing wrong?

Thanx in advanced
.............................................

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Me.Lblkey.Text = Request.QueryString("Key")
UpdateGrid()
End If
End Sub
Private Sub UpdateGrid()

Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("bin\users.mdb")&"
Me.OleDbSelectCommand1.CommandText = "Select * from Users where UserID = '" & Me.Lblkey.Text & "'"

Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.SelectCommand.CommandText = Me.OleDbSelectCommand1.CommandText
Me.OleDbDataAdapter1.Update(Me.DataSetCompanies1)
Me.OleDbDataAdapter1.Fill(Me.DataSetCompanies1)
Me.OleDbConnection1.Close()
Me.txtusername.DataBind()
Me.txtpassword.DataBind()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim MyCon As OleDb.OleDbConnection
Dim MyCommand As OleDb.OleDbCommand
Dim MySql As String

MyCon = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("bin\users.mdb")&"
MySql = "UPDATE Users SET Password=@Pas WHERE UserID = @ID"
MyCommand = New OleDb.OleDbCommand(MySql, MyCon)
MyCommand.Parameters.Add(New OleDb.OleDbParameter("@Pas", SqlDbType.Char, 50))
MyCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Pas").Value = Me.newtxt1.Text
MyCommand.Parameters("@ID").Value = Me.Lblkey.Text

MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()

UpdateGrid()

End Sub

 

[jbenson001]

What line of code does the error occur on?

Jim

 

[eulogy6]

Thanx jbenson001 from your reply..

The "Update" statment..

 

[ca8msm]

So you mean that it errors on the following line?

MyCommand.ExecuteNonQuery()

If so, it may be the way that Access handles parameters. I'm not sure (as I avoid access in web applications) but the syntax may use a question mark (?) rather than an "at" sign (@).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[eulogy6]

Yes the error is on the "MyCommand.ExecuteNonQuery()"line

The problem is that I can "Insert" or "Delete" if I change the Sql statment but I can't "Update"

Any Idea?

Thanx

 

[TipGiver]

Modify this to fit you. The commented; i dont know if it is useful.

Dim prm As New OleDbParameter()
prm.ParameterName = "Pas"
prm.OleDbType = OleDbType.Char

' prm.Precision = ?
' prm.Size = ?
' prm.Scale = ?

prm.SourceColumn = null
prm.Direction = ParameterDirection.Input
prm.Value = Me.newtxt1.Text

cmd.Parameters.Add(prm)

Also, at line "MyCon = New OleDb..." why it ends with &" ?

 

[ca8msm]

It should work if you implement the changes I suggested. I also notice that you are using a SqlDbType for an OleDb source, so you may also have to modify those to use the relevant Ole types.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Isadore]

The error originates from the SQL statement (when using Access if there is ANY problem in the SQL the code will break at the nonQuery line). It is a Typing or Syntax error.

 

[eulogy6]

thanx to all for your kind replay.

I made the changes but I have the same error.
"Syntax error in UPDATE statment"

Also, the line "MyCon = New OleDb..." end with &", because
I have also a Access password after that..

Any suggestion?

Thanx

 

[Isadore]

eulogy6: Not an SQL programmer myself but you can rest assure that the problem is indeed syntax - and there is something there that shouldn't be, or something missing (probabably a quote, single quote, etc).

For example, in the line:

MyCommand.Parameters("@ID").Value = Me.Lblkey.Text

Perhaps it should be:

MyCommand.Parameters("@ID").Value ='" & Me.Lblkey.Text & "'"

At any rate, your problem is strictly related to the syntax in the string that you are providing. Try doing a general search showing a complete UPDATE string for OLEDb - there must many example here at Tek-Tips and Google.

 

[ca8msm]

I made the changes but I have the same error.

Can you post the new code that you are using then?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[TipGiver]

I do not use either OLE or even more Access for database. The last i can post and hope it will help is the following:

Dim MySql As String = String.Format("UPDATE Users SET Password='{0}' WHERE UserID='{1}'",Me.newtxt1.Text,Me.LblHey.Text)