Query Help

[Rob1222]

I'm trying to create a simle database and I need help. I am trying to create a car inventory database. What I am trying to do is create a query that will prompt the user for data, search my vehicle table (ie. enter a license plate#) if a match is found then display that informationand allow you to add new information on the same record. If it is new then bring up a new record to inter information. Can somebody help me?

 

[ddivita]

I am not sire of your expertise with vb.net or what database you are using, but I will give you some pseudo code that may help. IF you need more specific examples, let me know. Let’s assume you are using MS access.
Database:

Cars Table:
	Columns:
CarID
Make
Model
LicensePlate

Search.aspx:

<asp:TextBox id=”tbLicense” runat=”server”/>
<asp:Button id=”btnSearch” Text=”Search” runat=”server”/>

Search.aspx.vb

btnSearch_Click Event:
	dim carDataConn as string = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;”
	dim carOle as new OleDbConnection(carDataConn)
	carOle.open()
	Dim daCars As New OleDbDataAdapter("SELECT * FROM Cars WHERE LicensePlate=’" & me.tbLicense.text & “’”, carOle)
	dim dsCars as new dataset
	daCars.Fill(dsCars)
	
if not dsCars is nothing andAlso dsCars.tables.count > 0 then
	dim carRow as dataRow = dsCars.Tables(0).Rows(0)
	response.Redirect(“SearchResults.aspx?carID=” & carRow(“carID”))
else
	Response.Redirect(“SearchResults.aspx?lpNumber=” & me.tbLicense.text
end IF

SearchResults.aspx

<asp:TextBox id=”tbMake” runat=”server”/>
<asp:TextBox id=”tbModel” runat=”server”/>
<asp:TextBox id=”tbLicense” runat=”server”/>
<asp:HiddenField id=”hfCarID” runat=”server”/>
<asp:HiddenField id=”hfPageMode” runat=”server”/>
<asp:Button id=”btnSave” Text=”Search” runat=”server”/>

SearchResults.aspx.vb

	Private _pageMode as string = String.Empty
	Page_load
		If not Request.QueryString is nothing then
			If not Request.QueryString(“lpNumber”) is nothing then
				‘We are adding a new Record
				me.hfPageMode.Value  = “New”
			Else
				dim carID = cint(Request.QueryString(“carID”)
				Call LoadCarData(carID)
				me.hfPageMode.Value  = “Update”
			End IF
		End IF
	
	Private Sub LoadCarData(byval carID as integer)
‘You will want to connect to your database and fill in the fields on the page.
me.hfCardID.value  = carID
	End Sub

	Private Sub SaveData()	handles me.btnSave.Click
_pageMode = me.hfPageMode.value
Select Case _pageMOde
Case “Update”
	‘All you code to update based off the me.hfCarID field
Case “New”
‘Insert a new record into the databse.
	End Sub

 

[Rob1222]

VB Help

Thanks for the response, is it possible I could send you a copy of what I am doing so you could see what I've done?

 

[ddivita]

Sure,

danielDOTdivitaATgmailDOTcom

 

[Rob1222]

I think the file is too big to send. I an using visual studio 2008 (visual basic), I have created a database called parking.mdf. parking.mdf has three tables (1 parent "violation" and two child tables) named "vehicles", "people". What I want to do is have the user to be prompt to enter a license plate number, run a query in the vehicle table, if there is a match bring up the record from all three tables. If there is no record then add a new record.

 

[ddivita]

Is this a web app or Winform App? I forogot to ask.

Daniel

 

[Rob1222]

It's a web app, I have a winform version but I wanted to us this one. If the winform is easier to work with then let me know.

 

[Rob1222]

Query

Any luck yet?

 

[ddivita]

Sorry, I am out of town, but I will try to get to it today, if I can.

Daniel

 

[ddivita]

Sorry I haven't gotten back to you I just gpt back into town. I have been quite busy. I will try to help you out today if you still need it. Again, sorry.

Daniel

 

[Rob1222]

Insert Record

Does anyone out there know how to insert a record in Visual Studio 2008 using the configure data quiry builder "insert tab"? If so please tell me how to do it!!

 

[cjard]

I'd be amazed if MS never wrote a tutorial. Take a look at the DW2 link in my signature; it's for 2005, but you'll find links off to 2008 related sections