Inserting id into databse from other table

[gopal2006]

Hi all

Here I have a problem in inseting a record into sql database. I have two tables cust & proj. In cust i have declared fields as cid int which is set as primary key & custname as varchar(30). In another table proj i have three fields. i.e. projid(primary key), proj_custid(which refernces cid from cust ie. foreign key) & proj name.

My problem is I have a form in my appication named 'project' with one combbox for customer name list which is fetched from form 'customer'. Here in the proect form project id is generated automatically.It is not be shown in the form. I have done uptil here properly. But when we save this form In the table 'proj' field 'proj_custid' should be filled with the 'cid' from the cust table. That is 'proj_custid' should be same as 'cid'

My Query runs like this
Dim sql As String = ("select cid from cust where custname= & cbxcustname.Text ")*'cbxcustname is name of combobox
sql=dr("cid")
custid=sql*declared custid as integer at the windows form generated designer code*
Inserted this custid in the insert query . But it is not working Can anyone help me out in this? This may be a very silly thing for all of you. But I am novice programmer. Please guide me

 

[jmcilhinney]

Look at your SQL code. You have literally put the name of the ComboBox into the string. You want to concatenate the value of the Text property with the string, so it has to be OUTSIDE the double quotes.

 

[gopal2006]

Can you just show me the sample code?

 

[jmcilhinney]

"select cid from cust where custname = '" & cbxcustname.Text & "'"

 

[gopal2006]

id not stored correctly in table.

Ok. I am clear with the query. But still I am sure have made some mistake in my code. Because of that any one custid from customer table is stored in proj table. The custid which should be stored is not stored properly. Just check my code & suggest correction. Since I am novice programmer please excuse me for this

sql = dr("cust_lid")
custid = sql
str = "insert into project values ('" & projid & "','" & custid & "','" & txtprojname.Text & "' ) "

 

[DavidT_macktool]

I'm not a real big fan of the concatinated string insert command. It may be alright for just a few fields but you should get used to using parameterized insert commands;

You can find examples here at VB.NET Forums
Data Adapter wizard will generate parameterized insert commands for you also.

Here is an example I found by searching for "insert statement"

cmd.CommandText = "INSERT INTO Categories (Category, Parent, Autor) VALUES (?, ?, ?)"
cmd.Parameters.Add(New OleDbParameter("Category", OleDbType.VarChar))
cmd.Parameters("Category").Value = Me.txtCategory.Text.Trim
cmd.Parameters.Add(New OleDbParameter("Parent", OleDbType.Integer))
cmd.Parameters("Parent").Value = Me.cmbCategory.SelectedValue
cmd.Parameters.Add(New OleDbParameter("Autor", OleDbType.Integer))
cmd.Parameters("Autor").Value = Request.Cookies("ManicCMS").Item("Autor").ToString


here is another example link:
http://www.vbdotnetforums.com/showpost.php?p=22115&postcount=4