Benj78

Hi all,

I am using VB express. I have created a customer database program attached to Sql database with a customer table, and column names CustomerCode, Name, Addra etc.

On the customer form I want to be able to enter a customer code in the CustomerCodeTextBox, if there is a row in the data with a matching code I want the program to show that row, if not add a new customer using the customer code I have entered.

I am new to Programming so I hope I have given enough info. Hope someone can help, i'll have no hair left by Saturday!!!!!!

Many Thanks.



Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

ahmedilyas

sure, no worries.

Well you can simply check to see if the value entered for the customer code exists in the database by performing a simple SELECT command. Perhaps filling a dataset/datatable with the details of that record, or using a SqlDataReader to read the record column by column and then outputting it to the screen in some way.

Just to make things easier for you, lets go with the idea of having a dataset/datatable.

import the System.Data and System.Data.SqlClient namespaces, so you can access the Sql classes as well as the datasets.

Next up, we need to fill a dataset with data from SQL, where the customer code entered matches. Example:

private sub cmdSearch_Click(byval sender as object, byval e as EventArgs) handles cmdSearch.Click

dim theSQLCommand as new SqlCommand("SELECT * FROM [TableName] WHERE [CustomerCode] = @customerCode", new SqlConnection("ConnectionStringHere")) 'We need to execute a select command in SQL so it will select the record, if it exists, where the customer code matches the textbox value you entered

Dim parameter as new SqlParameter("@customerCode", SqlDbType.Int, 4) 'we need to add a parameter. The parameter is good practice to use as its safer than concatinating your inputs into the SqlCommand select statement in regards to security and preventing it from doing SQL Injection attacks - lets not talk about this right now but something for you to search on should you wish to know more about it

parameter.Value = Me.CustomerCodeTextBox.Text 'set the parameter value to the value stored in the textbox for which we are going to be performing the search on, the customer code

theSQLCommand.Parameters.Add(parameter) 'add the parameter into the SQLCommand object parameter collection so it can look at the parameters to obtain its values to perform the search

Dim results as new DataSet() 'create a dataset object, this will store our results given to us from SQL

Dim theSQLDataAdapter as new SqlDataAdapter(theSQLCommand) 'in order to fill the dataset with results, we need to use a SqlDataAdapter object.

theSQLCommand.Connection.Open() 'open the connection to SQL

theSQLDataAdapter.Fill(results) 'fill the results/execute the query

theSQLCommand.Connection.Close() 'close the connection

Me.customerDetailsDataGridView.DataSource = results.Tables(0).DefaultView 'lets already hope there is a datagridview on your form to show the results found. We need to bind the results stored in the dataset to the datagridview so you can see the records

end sub

does this help

Now if you are wanting to add a customer code if the customer code does not exist you could simply check to see if the DataTable (results.Tables(0)) has rows, if not then we know it does not exist therefore execute an insert command into the database....

if results.Tables(0).Rows.Count = 0 then

Me.DoInsertCustomer(Me.CustomerCodeTextBox.Text)

end if

private sub DoInsertCustomer(byval customerCode as string)

Dim theSQLCommand as new SqlCommand("INSERT INTO [TableName] (CustomerCode) VALUES (@customerCode)", new SqlConnection("connectionString")) 'we need to execute the insert command into the database to create that new record giving it the customer number to insert

Dim parameter as new SqlParameter("@customerCode", SqlDbType.Int, 4)

parameter.Value = customerCode

theSQLCommand.Parameters.Add(parameter)

theSQLCommand.Connection.Open()

theSQLCommand.ExecuteNonQuery()

theSQLCommand.Connection.Close()

end sub

this will now do just that, insert a new record into the database and that record having the customerCode as a value for that field.

I hope this helps






Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

ReneeC

I wonder who checked the answer as being right






Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

Benj78

Hi,Thanks for your help, i'm am nearly there i think, just a couple of quick questions.

I already have a dataset called supermarketsupportdataset, a table adapter named customertableadapter and a customerbindingsource.

Does this change the results section of the code you gave me as all my textboxes are all linked to the adapter, this is where i will be displaying my data.

Thanks again.





Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

ahmedilyas

sure. Well mine was an example but shouldnt change too much. it shouldnt change as long as the appropriate SQL commands have been created in the dataset designer - you should be good to go :-)




Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

Benj78

I think i have finally got there thanks for your help!!!!

i have added the sql statement in the dataset designer and added this code to the keyup funtion, see below!

This seems to work, not sure if its great code but it works! Thanks Again.

Private Sub CustomerCodeTextBox_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles CustomerCodeTextBox.KeyUp

If e.KeyCode = Keys.Enter Then

Dim cuscodeparam As New SqlParameter("@cuscode", SqlDbType.Int)

cuscodeparam.Value = Me.CustomerCodeTextBox.Text

Try

Me.CustomerTableAdapter.cuscodesqlfill(Me.SupermarketSupportDataSet.Customer, CType(CustomerCodeTextBox.Text, Integer))

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try

End If

End Sub





Re: Visual Basic Express Edition Checking for existing data using txtbox entry.

ahmedilyas

glad you got it sorted - glad I could help!