VB.NET, MS Access, and parameters [RESOLVED]

[shadowfyre]

This is a duplicate of a thread I started in the DB forum. After thinking about it, I thought I might get more help here as there are more eyes on it.

So I'm going along, doing a fairly simple project for a software testing group that needs to communicate the defects they find. Basically the Access DB stores the defect number, date, and description. So I have :

Dim InsertData As String = "INSERT INTO Defects (DefectNumber,  DefectDescription, Date)" _
& "VALUES(" & defectnum & ", '" & TextBox4.Text & "', '" & TextBox3.Text & "');"

And it works great until someone used a double quote in the description (TextBox4.Text). So someone tells me to use parameters to get around that.

I bought Microsoft ADO.NET Step by Step and didn't help at all. Most of it's examples were based using SQL Server, not Access, and I understand that there is some difference.

Any advice on where I can get some helpful information?

[salvelinus]

I had this same problem. Single quotes would throw it off as well.
I think I fixed it by using Replace() to strip out the quotes. Another option is to check for quotes in a key event and not allow them in the first place.
If that doesn't help, I'll try looking it up at work tomorrow.

[chinhow]

in the access, you create a queries first which is something like:


INSERT INTO Job ( JobPO, JobCust, JobModel, JobSize, JobSubstance, JobColor, JobSet, JobSpeed, JobMin, JobOrder, JobPlanned, JobQty, JobReject, JobEff, JobMac, JobWorker, JobShift, JobDate, JobComments )
VALUES ([@NewJobPO], [@NewJobCust], [@NewJobModel], [@NewJobSize], [@NewJobSubstance], [@NewJobColor], [@NewJobSet], [@NewJobSpeed], [@NewJobMin], [@NewJobOrder], [@NewJobPlanned], [@NewJobQty], [@NewJobReject], [@NewJobEff], [@NewJobMac], [@NewJobWorker], [@NewJobShift], [@NewJobDate], [@NewJobComments]);


and in vb.net you have to pass in the parameters

Dim objCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("spInsertNewJob", OleDbConnection1)
        objCmd.CommandType = CommandType.StoredProcedure
 
        Dim objParam As OleDb.OleDbParameter = New OleDb.OleDbParameter()
        objParam = objCmd.Parameters.Add("@NewJobPO", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtPONumber.Text
 
        objParam = objCmd.Parameters.Add("@NewJobCust", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlCustomer.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobModel", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtModel.Text
 
        objParam = objCmd.Parameters.Add("@NewJobSize", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtSize.Text
 
        objParam = objCmd.Parameters.Add("@NewJobSubstance", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtSubstance.Text
 
        objParam = objCmd.Parameters.Add("@NewJobColor", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlColor.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobSet", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtSettingTime.Text
 
        objParam = objCmd.Parameters.Add("@NewJobSpeed", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtSpeed.Text
 
        objParam = objCmd.Parameters.Add("@NewJobMin", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtPrintingTime.Text
 
        objParam = objCmd.Parameters.Add("@NewJobOrder", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtOrderQuantity.Text
 
        objParam = objCmd.Parameters.Add("@NewJobPlanned", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtPlannedQuantity.Text
 
        objParam = objCmd.Parameters.Add("@NewJobQty", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtActualQuantity.Text
 
        objParam = objCmd.Parameters.Add("@NewJobReject", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtRejectQuantity.Text
 
        objParam = objCmd.Parameters.Add("@NewJobEff", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtEfficiency.Text
 
        objParam = objCmd.Parameters.Add("@NewJobMac", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlMachine.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobWorker", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlWorker.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobShift", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlShift.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobDate", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtDate.Text
 
        objParam = objCmd.Parameters.Add("@NewJobComments", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtComment.Text

Just let me know in case you don't understand.

[salvelinus]

But wouldn't there still be problems with quote marks in the textboxes?

[shadowfyre]

salvelinus, that's the whole point of using parameters. You can get double and single quotes into the DB with out the query bombing out. At least that is what I've read. I guess I'll find out.

I've never really used Access before. I've figured out how to sorts and queries using that little table that lets you choose which columns you want to show and how you want them sorted, but I've never done an Insert in Access before. I've only done inserts and updates via VB as I've shown above.

[salvelinus]

Well, maybe so, but I think Replace() would be less code.
I don't use the Access Query Designer, I switch to SQL view to just write the SQL. It's easier to test SQL from your code that way.

[shadowfyre]

You're probably right about the Replace() being the easier way to do it, but there are some cons to doing it that way. First of all, I plan to display this information to other people. If someone needs to use a double or single qoute in the defect description, I would have to replace them when I put them into the DB with another couple characters, and then put them back in when I display the description at a later time. So that forces me to declare two other characters as unusable to my user base. Secondly, I guess the parameter way is the more "professional" way, and since this is for work, I should probably do it that way. Finally, since I don't know how to use parameters, this gives me a great oppurtunity to learn.

Back to the subject at hand. I have the INSERT command in my database. How do I execute it while passing all these parameters?

[chinhow]

You can see at here:

Dim objCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("spInsertNewJob", OleDbConnection1)
        objCmd.CommandType = CommandType.StoredProcedure

Above code is actually call the MS Access Queries name "spInsertNewJob"


Now code below is actually passing all the neccessary parameters to the MS Access Queries.

Dim objParam As OleDb.OleDbParameter = New OleDb.OleDbParameter()
        objParam = objCmd.Parameters.Add("@NewJobPO", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtPONumber.Text
 
        objParam = objCmd.Parameters.Add("@NewJobCust", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = ddlCustomer.SelectedItem.Text
 
        objParam = objCmd.Parameters.Add("@NewJobModel", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = txtModel.Text
 
...

hope that answered your question. I found this from a book which is "SAM Teach yourself ASP.NET In 21 Days", examples of asp.net pages' code behind are mainly vb.net. Moreoever, this book shows you how to handle the "Transaction". Few Sql statement combine to become a TRANSACTION. If any one of the statement goes wrong, the Data will be rolled back to the initial stage.

[chinhow]

sorrie, for execution part is

Try
            OleDbConnection1.Open()
            objCmd.ExecuteNonQuery()
   
            Me.ClearAllEntry()
        Catch ex As OleDb.OleDbException
            Response.Write(ex.Message)
        Finally
            OleDbConnection1.Close()
        End Try
    End Sub

[shadowfyre]

Beautiful. I think I've got it now. Just for completeness sake here is the relavent chunck of my code

Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source = C:\old\DBTEST\PatchMan_test.mdb;"
        Dim oOleDbConnection As New OleDb.OleDbConnection(sConnString)
        'Dim InsertData As String = "INSERT INTO Defects (DefectNumber, DefectVersion, DefectArea, DefectDescription, RbApprovedDate, UserRb)" _
        '                        & "VALUES(" & defectnum & ", '" & VersionNum & "', '" & Component & "', '" & TextBox4.Text & "', '" & TextBox3.Text & "', '" & TextBox2.Text & "');"
 
        Dim myOleCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand("AddDefect", oOleDbConnection)
        myOleCommand.CommandType = CommandType.StoredProcedure
        Dim objParam As OleDb.OleDbParameter = New OleDb.OleDbParameter
 
        objParam = myOleCommand.Parameters.Add("@DefectNumber", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = defectnum
 
        objParam = myOleCommand.Parameters.Add("@DefectArea", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = Component
 
        objParam = myOleCommand.Parameters.Add("@DefectVersion", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = VersionNum
 
        objParam = myOleCommand.Parameters.Add("@DefectDescription", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = TextBox4.Text
 
        objParam = myOleCommand.Parameters.Add("@RbApprovedDate", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = TextBox3.Text
 
        objParam = myOleCommand.Parameters.Add("@UserRb", OleDb.OleDbType.BSTR)
        objParam.Direction = ParameterDirection.Input
        objParam.Value = TextBox2.Text
 
        Try
            oOleDbConnection.Open()
            MessageBox.Show("connection open", "connection open")
            myOleCommand.ExecuteNonQuery()
            MessageBox.Show("execute insert", "execute insert")
            MessageBox.Show("Defect " & TextBox1.Text & " has been added.", "Success")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Caught Exception")
        Finally
            oOleDbConnection.Close()
        End Try

Thanks chinhow.

salvelinus, just for future reference, it does preserve double and single quotes.

[chinhow]

You are welcome, It's my pleasure