problem with search engine

[plumage]

i am doing search engine and can i have problem retriving data in the textfields after i press the search button.

here the code i write
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\Yee\My Documents\Assignment2.mdb"



Dim MySQL As String = "select* from Customer" & _

"where CustomerNRIC = 'txtNRIC'"



Dim MyConn As New OleDbConnection(strConn)

Dim cmd As New OleDbCommand(MySQL, MyConn)

MyConn.Open()

'-- CHECK FOR VALID RECORD ---

Dim ValidRecord As Boolean = True

'-- Check for missing customer nric

If txtNRIC.Text = "" Then

MsgBox("Missing customer NRIC")

ValidRecord = False

End If

Try

cmd.ExecuteNonQuery()

MyConn.Close()

 

[plumage]

btw wat is the use of Dim reader As OleDbDataReader = cmd.ExecuteReader?

 

[Paszt]

In your first post, you use the ExecuteNonQuery method of a command object. This method Executes an SQL statement against the connection specified in the command object and returns an integer which represent the number of rows affected. The ExecuteNonQuery doesn't return data from the database. This method is usually used when you are executing an INSERT or UPDATE command.

If you want to return data there are several options depending on how much data you are retrieving and how you want to store that data in memory. If you want to store the data in a dataset, use a DataAdapter and it's Fill method. If you are returning a small amount of data and want to store it in variables, use a dataReader. If you are returning only one field of one record, use the ExecuteScalar method of the command object.

 

[plumage]

Thanks alot...i try the datareader,it really work.thanks for your advice.

if i search and display the data in the datagrid,and iszit possible to have the data appearing at a new form after i click on a particular record that i want to edit? so that i can make editing easier?

thanks

 

[plumage]

now i manage to search the data that i need, but the thing is when i wan to edit the record,and after i finish editing and how do i update the record to the database?

 

[Paszt]

This is where the ExecuteNonQuery method of the DataCommand object comes in handy.

Here is an example which uses a DataCommand and parameters:

Dim strSQL As New System.Text.StringBuilder()
strSQL.Append("INSERT INTO ForumThreads ")
strSQL.Append("(CategoryID, Title, Message, UserID) ")
strSQL.Append("VALUES (mCategoryID, mTitle, mMessage, mUserID)")

Dim cmd As New OleDbCommand(strSQL.ToString, cnn)
With cmd.Parameters
    .Add("mCategoryID", txtCategoryID)
    .Add("mTitle", txtTitle)
    .Add("mMessage", txtMessage)
    .Add("mUserID", txtUserID)
End With

Try
    cnn.Open()
    If cmd.ExecuteNonQuery <> 1 Then
        'it didn't work
    Else
        'it worked
    End If
Catch exc As Exception
    'it didn't work
Finally
    Try
        cnn.Close()
    Catch : End Try
    cmd.Dispose()
End Try

In this example cnn is a DataConnection that has already been instantiated and had it's connectionString set. I check to see if the returned value of the ExecuteNonQuery method is not 1 because I'm assuming that only one record will be affected.

You can do this without parameters (in the SQL statement you would set the values instead of the parameter name). However, using parameters will make things simpler if the values have characters such as quotes in them.

 

[plumage]

i was trying with this following code to update record to the database.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\YangLim\My Documents\Assignment2.mdb"

Dim MyConn As New OleDbConnection(strConn)

Dim MySQL As String

MySQL = "UPDATE Customer SET CustomerID = '" + txtCustID.Text + "', CustomerName = '" + txtName.Text + "', CustomerAddress = '" + txtAddress.Text + "', CustomerPostCode = " + txtPostcode.Text + ", CustomerGender = '" + cboGender.Text + "', CustomerContactNo = '" + txtContactNo.Text + "', CustomerDOB = '" + txtDOB.Text + "', CustomerNRIC = '" + txtNRIC.Text + "' WHERE CustomerNRIC = '" + txtNRIC.Text

Dim cmd As New OleDbCommand(MySQL, MyConn)

MyConn.Open()

cmd.ExecuteNonQuery() ------(but error appear here)

MyConn.Close()

**can this way work too?**

thanks for ur help,

 

[levyuk]

I think this is because ExecuteNonQuery() returns how many rows have been affected by the sql. So you will need to do something like this

Dim SQLReturn as Integer
SQLReturn = cmd.ExecuteNonQuery()

That should solve your problem

 

[Paszt]

You can do as levyuk suggests to make sure that the expected number of records are affected, however this is not necessary.

If you look at the error message, it most likely says that there is an error in the SQL statement. For items that are numbers in the database, you don't need to enclose them in single quotes. Also the CustomerNRIC value has an opening single quote but no closing quote; you can remove the first quote. Also, if the CustomerDOB field is of date type in the database, you may need to enclose it in pound signs (#).

Something like this:

MySQL = "UPDATE Customer SET CustomerID=" & txtCustID.Text & ", CustomerName='" & txtName.Text & _
        "', CustomerAddress='" & txtAddress.Text & "', CustomerPostCode=" & txtPostcode.Text & _
        ", CustomerGender='" & cboGender.Text & "', CustomerContactNo=" & txtContactNo.Text & _
        ", CustomerDOB=#" & txtDOB.Text & "# WHERE CustomerNRIC=" & txtNRIC.Text

 

[plumage]

now, i am able to update record,but i have some problem.

if my nric has some relationship with other table like rental, when i update the record,how can i let it automatic update the record in the rental table part too?

secondly- outside my groupbox, i have the txtbox(customerNRIC) and a search button.
then inside my groupbox, i have the detail of the customer include the customer nric again.
the thing is when i edit the customer nric, it can be updated in the database and the the txtbox itself inisde the groupbox.now the problem is when i update the customer nric,can i make it automatic to update the customer nric txtbox outside the groupbox too?
how can it be done?




thanks. alot..