[deery5000]

Hey guys im new to Vb.net

Im trying to do an assignment at uni and it requires me to create a database and search the table
pretty simple right

well. . . .

Ive created a table CustomerContact

I want the user to input AccntRepNo and click a search button.
ill use this number to search (query) my table dispaying all the records
related to the AccntRepNo

Can anyone help me with this?

Kevin

Pls look at pics attached
Ive been reading alot about connections but i already have a database setup

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		Dim repNo As String
		repNo = AccntRepTextBox.Text

		' Me.CustomerContactsTableAdapter.WHAT TO DO HERE




	End Sub

Attached image(s)

• 
• 

[Core]

Add a reference to System.Data.SqlClient and use a connection string specific for your RDBMS. Use this resource to find the needed connection string.

Then, you can execute an SQL query like this:

SELECT * FROM Table WHERE Criteria

And use the SqlDataReader to get the returned result.

[deery5000]

Hi mate thanks for the response. I was trying these with no luck

		Dim connetionString As String
		Dim cnn As SqlConnection
		Dim cmd As SqlCommand
		Dim sql As String

		Dim repNo As String
		repNo = AccntRepTextBox.Text


		connetionString = "Data Source=Dell_XPS_1340;Initial Catalog=CompanyDatabase"
		sql = "SELECT RecordNo, Company, CustNumber, Person, Subject, AccntRep FROM(CustomerContacts)WHERE   (AccntRep = RepNo)"

		cnn = New SqlConnection(connetionString)
		Try
		   
			cnn.Open()
			cmd = New SqlCommand(sql, cnn)

			Dim reader As SqlDataReader = cmd.ExecuteReader()

		 'how do i display my results in my table? Datsview

			cmd.Dispose()
			cnn.Close()



		Catch ex As Exception
			MsgBox("Can not open connection ! ")
		End Try

2 problems

1. Im not sure if my connection string is correct.
2. How do i display my data (datagrid is called CustomerContactsDataGridView)

So Close
Kevin

This post has been edited by deery5000: 02 November 2009 - 12:08 PM

[Core]

Well seems like you have some problems with the connection string and the SQL query. The connection string should look like this:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

The query should be like this:

sql = string.Format ("SELECT * FROM CustomerContacts WHERE  AccntRep = '{0}'", repNo);

Then, just use the SqlDataReader to read the response:

 SqlDataReader reader = command.ExecuteReader();

		// Call Read before accessing data.
		while (reader.Read())
		{
			// Pseudocode to add item to table
			table1.Items.Add (reader[0]);
		}

[deery5000]

Dim connetionString As String
		Dim cnn As SqlConnection
		Dim cmd As SqlCommand
		Dim sql As String

		Dim repNo As String
		repNo = AccntRepTextBox.Text


		connetionString = "Data Source=Dell_XPS_1340;Initial Catalog=CompanyDatabase"
		'sql = "SELECT RecordNo, Company, CustNumber, Person, Subject, AccntRep FROM(CustomerContacts)WHERE   (AccntRep = RepNo)"

		sql = String.Format("SELECT * FROM CustomerContacts WHERE  AccntRep = '{0}'", repNo)

		cnn = New SqlConnection(connetionString)
		Try
		   
			cnn.Open()
			cmd = New SqlCommand(sql, cnn)

			Dim reader As SqlDataReader = cmd.ExecuteReader()

			While (reader.Read())


				CustomerContactsBindingSource.Add(reader)

			End While


			cmd.Dispose()
			cnn.Close()



		Catch ex As Exception
			MsgBox("Can not open connection ! ")
		End Try

i dont know how to display the results in the datagrid

ther server name is dell_xps_1340 (my computer), the databse name is CompanyDatabase and i dont have any login details

reader[0] it didnt like this

Kevin

I dont think im opening the connection correctly
have a look at the pic

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

i dont have things that need to be in my connection string, my database is local.
Look at my pic

Attached image(s)

• 
• 

This post has been edited by deery5000: 02 November 2009 - 01:27 PM

[AndyH1963]

Does this simple example help. I just created a new project added a DataGridView to the form and set the datasource to be the NorthWind.mdb which I placed on the root of my C:\ drive:

Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb"
Dim cnn As OleDbConnection = New OleDbConnection(strCon)

The following code although uses an OleDb similar code can be used for SQL Server and Oracle data providers

Try
   Dim da As OleDbDataAdapter = New OleDbDataAdapter("select * from customers", cnn)
   Dim ds As DataSet = New DataSet
   ' get all data from custmers table:
   da.Fill(ds, "customers")
   ' attach DataSet to DataGrid
   DataGridView1.DataSource = ds.Tables(0)

Catch ex As Exception
   MsgBox("Unable to open database", MsgBoxStyle.Exclamation)
End Try

HTH

[deery5000]

Thanks andy