New Posts  All Forums:Forum Nav:

Working with SQL in VS 2010

post #1 of 2
Thread Starter 
I've been working in Visual Studio 2010 for about 2 weeks now on the first real project I've had to use this language for in many years, and I can't seem to get a couple things working.

(1) I'm searching a SQL Database using OleDB through multiple tables to generate results. I need to then compile these results into a listbox that let's the user select the result they wanted, click it and have it open up the form with existing fields already populated.

The actual search looks like this. SrcReq is the search term (it can span about 10 columns and it is built based off of what info you fill in on the search form.
Quote:
cmd.CommandText = "SELECT * From AreaMic_Manufacture_Complete WHERE " & SrcReq
cmd.Connection = con
con.Open()
rd = cmd.ExecuteReader()
While rd.Read
s = rd.GetString(0) & vbTab & rd.GetString(5)
End While
ListBox1.Items.Add(s)
Basically this pulls data from a DB based on whatever search terms a user enters, then returns abbreviated info on the products that match the search query from the database (they can search by a range of manufacture dates, etc). I need a way to select a row in the ListBox, hit a button and have it populate the entire entry in the database back into a form for viewing.

(2) I also need to make a Windows Form that will let the user browse through one Table in a database, select an entry, add a little more info, hit the submit button and have it delete the row out of the Table it pulled the data from and submit the completed entry to a new Table. I've been trying to accomplish this by using the TableBinding controls, but it's not working very well, so I'm thinking I'll have to change it over to a ListBox functionality similar to my first problem unless anybody has a method to do this using DataBound controls.

Thanks for any help you can provide, because I've been searching around for help on this for a couple days now and haven't made much headway on it.
Maximum Dwarf
(15 items)
 
Density!
(12 items)
 
 
CPUMotherboardGraphicsRAM
i5-3570k ASUS MAXIMUS IV GENE HD7970 Gen 1 Ripjaws Z 2x8GB 2133MHz 
Hard DriveHard DriveOptical DriveCooling
1TB RE4 2x3TB WD Red LG 10x BD-R Corsair H80i w/push pull 
OSMonitorKeyboardPower
Win 7 Ultimate 3x 1920x1080 LG IPS displays. Razer Mass Effect 3 Blackwidow Ultimate Cooler Master Silent Pro M850 
CaseMouseMouse Pad
Silverstone TJ08B-E R.A.T. 7 An ergonomic one 
CPUMotherboardRAMHard Drive
i7 3770k Gigabyte Sniper M3 1155 mATX CORSAIR Vengeance 8GB (2 x 4GB) 1600MHz 1TB WD RE4 
Hard DriveHard DriveHard DriveHard Drive
2TB WD Red 2TB WD Red 4TB WD Red 4TB WD Red 
PowerCaseAudioOther
750W Seasonic Gold Fractal Node 804 5.1 Definitive Def Tech PERC 5i RAID card w/ BBU (LSI Firmware) 
  hide details  
Reply
Maximum Dwarf
(15 items)
 
Density!
(12 items)
 
 
CPUMotherboardGraphicsRAM
i5-3570k ASUS MAXIMUS IV GENE HD7970 Gen 1 Ripjaws Z 2x8GB 2133MHz 
Hard DriveHard DriveOptical DriveCooling
1TB RE4 2x3TB WD Red LG 10x BD-R Corsair H80i w/push pull 
OSMonitorKeyboardPower
Win 7 Ultimate 3x 1920x1080 LG IPS displays. Razer Mass Effect 3 Blackwidow Ultimate Cooler Master Silent Pro M850 
CaseMouseMouse Pad
Silverstone TJ08B-E R.A.T. 7 An ergonomic one 
CPUMotherboardRAMHard Drive
i7 3770k Gigabyte Sniper M3 1155 mATX CORSAIR Vengeance 8GB (2 x 4GB) 1600MHz 1TB WD RE4 
Hard DriveHard DriveHard DriveHard Drive
2TB WD Red 2TB WD Red 4TB WD Red 4TB WD Red 
PowerCaseAudioOther
750W Seasonic Gold Fractal Node 804 5.1 Definitive Def Tech PERC 5i RAID card w/ BBU (LSI Firmware) 
  hide details  
Reply
post #2 of 2
I don't have IDE here, so need to verify the syntax.

1. set the listbox selectionmode to One in property windows or in code, Me.ListBox1.SelectionMode = SelectionMode.One, then
in the button click event, add

' the another form that you want to disaplay entry
' define a public property dt
Public Property DT as DataTable
'get and set here
end property


If Me.ListBox1.SelectedItem IsNot Nothing Then
Dim newsearch As String = "SELECT * From AreaMic_Manufacture_Complete WHERE keycolname='" & Me.ListBox1.SelectedValue.ToString & "'"
' code here like one in your code
dim dt as new DataTable
dt.Load(datareaderobject)
' pass the datatable to form so it populate data
dim frm as new anotherform
with anotherform
.DT=dt
.showdialog
.dispose

end with

End If

' in the anotherform load event
with dt.rows(0)
me.txtid.text=.item("colname").tostring
....
end with




2. You cab use datagridview control, many ways to do this but I use what you have
dim dt as new DataTable ' in the form level
dim da as oledbdataadapter ' in the form level

da=new sqldataadpter(cmd)
da.fill(dt)

' drop a datagridview in form
datagridview.datasoruce=dt

'make sure the datagridview can edit,
' user edit something in datagrid,
' in the submit button click event
' to figure out which one is selected in datagridview, use selectcell or selectrows property
' if the table doesn't contain may columns you can use
dim cmd2 as oledbdataadapter("delete from table1 where colid='" & vlauefromdatagridview & "")
cmd2.EndExecuteNonQuery
' then build an insert sql string
dim strs as string="insert into newtable (fldname...) values (value1,...)"
' create a new command object and call EndExecuteNonQuery insert a new record
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Application Programming