SQLDataAdapter Insert Command

[keko2004]

hey guys. i wrote this select statements to draw out data from 2 tables.

[SIZE=2][COLOR=#ff0000]"SELECT Customers_CD.customer_id, Customers_CD.phone_number, Customers_CD.email_ad"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"dress, Customers_CD.customer_first, Customers_CD.customer_last,Customer_Address_"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"CD.address_id, Customer_Address_CD.address_1, Customer_Address_CD.town_city, Cus"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"tomer_Address_CD.state_county, Customer_Address_CD.zip_postal_code FROM Customer"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"s_CD INNER Join Customer_Address_CD ON Customers_CD.customer_id = Customer_Addre"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"ss_CD.customer_id "
[/COLOR][/SIZE]

all this will be set into a datagrid, except the address_id. i dont want to show this. now when i use my insert statement, i get an error saying address_id cannot be null. i understand that htis is happening because im not giving it any value. so how can i go about giving it a value behind the scenes, without show the column in the grid? heres my insert query.

[SIZE=2][COLOR=#ff0000]"INSERT INTO Customers_CD(customer_id, phone_number, email_address, customer_first"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]", customer_last) VALUES (@customer_id, @phone_number, @email_address, @customer_"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"first, @customer_last); SELECT customer_id, phone_number, email_address, custome"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"r_first, customer_last FROM Customers_CD WHERE (customer_id = @customer_id); INS"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"ERT INTO Customer_Address_CD(address_id, customer_id, address_1, town_city, stat"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"e_county, zip_postal_code)VALUES (7770, @customer_id, @address_1, @town_c"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"ity, @state_county, @zip_postal_code); SELECT address_id, customer_id, address_1"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]", town_city, state_county, zip_postal_code FROM Customer_Address_CD WHERE (addre"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#ff0000]"ss_id = @address_id);"
[/COLOR][/SIZE]

 

[Timothy Chan]

you may check your code

with dataadapterInsertCommand.paraters.add("@p1", oledbtype.varchar)
.sourcecolumn="address_1"
end with

In my experience, null value in string type field is acceptable as other string type fields in your SQL statement, so I guess the places going wrong maybe you have typed the varchar wrongly to other type which allow no NULL value, or you have set a wrong type in database. By the way, did you set the field in you database to allow NULL value?

I hope this would be helpful. Good Luck