[joshuaobelenus]

I have a form as shown below:

and a database as shown below:


It is an inventory program I am doing on the side just to keep track of a small number of supplies, I want to be able to enter a barcode and have the program see if it already is in the inventory database, if not, it will allow me to enter information and add it to the database (That part works - Thanks to member on D.I.C.)
If the barcode exists, I want it to fill the form with all the data from the database and allow me to update fields such as date used and used definition

Here is what I have so far:

Imports System
Imports System.IO
'Added For Database
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
	Dim SerialCallLogDatabaseLocation As String = "c:\database1.mdb"
	Dim SerialCallLogTableName As String = "Table1"
	Dim SerialCallLogDatabaseName As String = "Database1"
	Dim Serialinc As Integer
	Dim SerialMaxRows As Integer
	Dim Serialcon As New OleDb.OleDbConnection
	Dim Serialds As New DataSet
	Dim Serialda As OleDb.OleDbDataAdapter
	Dim Serialsql As String

	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

		'Entry
		Dim Serialcb As New OleDb.OleDbCommandBuilder(Serialda)
		Dim SerialdsNewRow As DataRow

		Try

			SerialdsNewRow = Serialds.Tables(SerialCallLogDatabaseName).NewRow
			SerialdsNewRow.Item("ID") = txtBarcode.Text
			SerialdsNewRow.Item("InvoiceNumber") = txtInvNum.Text
			SerialdsNewRow.Item("OrderNumber") = txtOrdNum.Text
			SerialdsNewRow.Item("InvoiceDate") = txtInvDate.Text
			SerialdsNewRow.Item("ItemDescription") = txtItem.Text
			SerialdsNewRow.Item("ItemCost") = txtCost.Text
			SerialdsNewRow.Item("DateUsed") = txtDateUsed.Text
			SerialdsNewRow.Item("OfficeUsed") = txtOffice.Text
			SerialdsNewRow.Item("SerialNumber") = txtCompSer.Text
			SerialdsNewRow.Item("UsedDefinition") = txtHowUsed.Text
			SerialdsNewRow.Item("UPCIfThere") = txtUPC.Text
			SerialdsNewRow.Item("SerialNumberIfThere") = txtSerNum.Text
			Serialds.Tables(SerialCallLogDatabaseName).Rows.Add(SerialdsNewRow)
			Serialda.Update(Serialds, SerialCallLogDatabaseName)

		Catch ex As Exception
			MsgBox(ex.ToString)
		End Try
		'Reset
		txtBarcode.Clear()
		txtInvNum.Clear()
		txtOrdNum.Clear()
		txtInvDate.Clear()
		txtItem.Clear()
		txtCost.Clear()
		txtDateUsed.Clear()
		txtOffice.Clear()
		txtCompSer.Clear()
		txtHowUsed.Clear()
		txtUPC.Clear()
		txtSerNum.Clear()
		txtBarcode.Focus()

	End Sub

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		'Connect
		Serialcon.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & SerialCallLogDatabaseLocation
		Serialcon.Open()
		Serialsql = "SELECT * FROM " & SerialCallLogTableName
		Serialda = New OleDb.OleDbDataAdapter(Serialsql, Serialcon)
		Serialda.Fill(Serialds, SerialCallLogDatabaseName)
		Serialcon.Close()
		SerialMaxRows = Serialds.Tables(SerialCallLogDatabaseName).Rows.Count
		Serialinc = -1
	End Sub

	Private Sub btnLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLookup.Click
		Dim SerNum As String
		Dim NumRec As String
		Dim BarSql As String

		SerNum = txtBarcode.Text
		If IsNumeric(SerNum) And SerNum.Length = 6 Then
			Serialcon.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & SerialCallLogDatabaseLocation
			BarSql = "Select Count(*) from " & SerialCallLogTableName & " Where ID = " & SerNum

			Dim cmd As New SqlCommand("Select Count(ID) from " & SerialCallLogTableName, New SqlConnection(Serialcon.ConnectionString))
			'ERROR! in above line
			'This is where I get "Keyword not supported: 'provider'" when I press the lookup button

			cmd.Connection.Open()
			NumRec = cmd.ExecuteScalar

			If NumRec = 0 Then
				MsgBox("Item Does Not Exist", MsgBoxStyle.Information, "New Item")
				Return
			Else
				MsgBox("Item Exists", MsgBoxStyle.Information, "Database")
				'This is where I want to fill in form
				'so for example, if I enter barcode 300252
				'it will look for ID = 300252 and if it exists, it will
				'pull InvoiceNumber from database and put it in txtInvoice
				'(like txtInvoice.text = data(InvoiceNumber)
			End If

		End If
	End Sub

	Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
		'This is where I want to be able to update the data in the dataset with any changes
		'from the lookup command above
		'so for example, I lookup barcode 300252
		'it pulls the invoice number from barcode 300252 and puts it in txtInvoice
		'I change the invoice number (say from 3008845 to 3238854)
		'I hit "update", it saves the new invoice number to barcode 300252
	End Sub
End Class

As you can see by my comments, Im running into a few snags.
Any and all suggestions are appreciated
Thank you in advance

-Josh

[nofear217]

Looks like you didn't finish off the SQL statement (or the compiler is reading that everything after the ampersand is part of it):

Dim cmd As New SqlCommand("Select Count(ID) from " & SerialCallLogTableName, New SqlConnection(Serialcon.ConnectionString))

should be

Dim cmd As New SqlCommand("Select Count(ID) from " & SerialCallLogTableName & "", New SqlConnection(Serialcon.ConnectionString))

A better solution would be to place your select statement into a string variable and then use the string for the parameters so that there is no confusion where the statement begins and ends.

This post has been edited by nofear217: 30 December 2008 - 08:45 AM

[joshuaobelenus]

I am continuing to get "Keyword not supported" I have tried researching that and come up with nothing. I have tried the following in response to your suggestion:

	Private Sub btnLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLookup.Click
		Dim SerNum As String
		Dim NumRec As String
		Dim BarSql As String

		SerNum = txtBarcode.Text
		If IsNumeric(SerNum) And SerNum.Length = 6 Then
			Serialcon.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & SerialCallLogDatabaseLocation
			BarSql = "Select Count(*) from " & SerialCallLogTableName & " Where ID = " & SerNum

			Dim SelSta As String
			SelSta = "Select Count(ID) from 'Table1'"
			Dim cmd As New SqlCommand(SelSta, New SqlConnection("Microsoft.Jet.OLEDB.4.0;Data Source = " & SerialCallLogDatabaseLocation))
			'Dim cmd As New SqlCommand(SelSta, New SqlConnection(Serialcon.ConnectionString))
			'Dim cmd As New SqlCommand("Select Count(ID) from " & SerialCallLogTableName & "", New SqlConnection(Serialcon.ConnectionString))
			'Dim cmd As New SqlCommand("Select Count(ID) from " & SerialCallLogTableName, New SqlConnection(Serialcon.ConnectionString))
			'ERROR! in above line
			'This is where I get "Keyword not supported: 'provider'" when I press the lookup button

			cmd.Connection.Open()
			NumRec = cmd.ExecuteScalar

[joshuaobelenus]

in the above commented out entries, all came back with keyword not supported, all indicating the connection string at fault

in SelSta i have tried using single quotes and no quotes around ID and Table1 with the same results

[nofear217]

Couple things that I wasn't paying attention to, you are using an Access database so when you are setting up your connection string it needs to be setup as an OleDb connection not SQL, here is your code changed around a bit and it seems to work for me:

    Private Sub btnLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLookup.Click

        Dim NumRec As Integer
        Dim SerNum As String
        Dim BarSql As String

        SerNum = txtBarcode.Text

        If IsNumeric(SerNum) And SerNum.Length = 6 Then

            Dim conn As New OleDb.OleDbConnection
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb"
            Dim SelSta As String = "Select Count(ID) from Table1"
            Dim cmd As New OleDb.OleDbCommand
            cmd.CommandText = SelSta
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn

            cmd.Connection.Open()
            NumRec = cmd.ExecuteScalar
            cmd.Connection.Close()

            If NumRec = 0 Then
                MsgBox("No items returned")
            Else
                MsgBox("There's stuff there")
            End If

        End If

    End Sub

You're getting the keyword Provider not supported because it's not in a SqlConnection.

This post has been edited by nofear217: 30 December 2008 - 11:06 AM

[joshuaobelenus]

I added a 'where ID = " & SerNum' to the end of the statement and it works like a charm.
I see my fault, I copied part of this code from an old project (still choking on dust) that I did in college that used a SQL server, such a small thing that makes a big difference.

Thank you so much, this project (that was supposed to be an hour long lunch time boredom solution) has been such a hassel until now.

You dont know how to grab data from Access to shove into my text boxes do you?

Thank you again.
-Josh

[nofear217]

If you are wanting to return the tabular data you will need to incorporate a data reader or you can find other possible solutions here. It was my pleasure to help. If you wish to show appreciation, just click the button in the bottom left of the post.