| Author |
Topic |
|
saidev
Posting Yak Master
101 Posts |
 Posted - 2006-07-05 : 18:59:32
|
| Hi Guys,I am using dbreader for inserting data into the table. But if there is any null value in one of the fields i am getting the message " Cast from DBNull to type string is not valid". How to get rid of this Null values.Appreciate your help. Here is my QuerySQL = "insert into tblcontract(fkcontracttype,fkgroup,fksubgroup,orderid,fkordertype,date,orderbegindate,orderenddate,fknetwork,estimatenumber,flight,donovan,fkadvertiser,fkagency,fktrafficsystem,agencycommission,buyer,fkclient,repcommission,fkproducttype,product,buydesc,fkae,fkae2,aesplit,natsplit,referencepkid)" & _" values ('UNWIRED', '" & dbreader("fkgroup") & "','" & dbreader("fksubgroup") & "','" & dbreader("orderid") & "','" & dbreader("fkordertype") & "','" & dbreader("date") & "','" & dbreader("orderbegindate") & "','" & dbreader("orderenddate") & "','" & dbreader("fknetwork") & "','" & dbreader("estimatenumber") & "','" & dbreader("flight") & "','" & dbreader("donovan") & "','" & dbreader("fkadvertiser") & "','" & dbreader("fkagency") & "', '" & dbreader("fktrafficsystem") & "','" & dbreader("agencycommission") & "','" & dbreader("buyer") & "','" & dbreader("fkclient") & "','" & dbreader("repcommission") & "','" & dbreader("fkproducttype") & "','" & dbreader("product") & "','" & dbreader("buydesc") & "','" & dbreader("fkae") & "','" & dbreader("fkae2") & "','" & dbreader("aesplit") & "','" & dbreader("natsplit") & "','" & dbreader("pkid") & "')"Thanks, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 19:26:39
|
I think you have to do something like this. I am sure VB.Net has better ways to deal with NULLs. Maybe even a function would be a good solution.SQL = "insert into tblcontract (fkcontracttype, fkgroup, fksubgroup, orderid, fkordertype, date, orderbegindate, orderenddate, fknetwork,"SQL = SQL & ", estimatenumber, flight, donovan, fkadvertiser, fkagency, fktrafficsystem, agencycommission, buyer, fkclient, repcommission"SQL = SQL & ", fkproducttype, product, buydesc, fkae, fkae2, aesplit, natsplit, referencepkid) VALUES ('UNWIRED'"IF ISNULL(dbreader("fkgroup")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkgroup") & "'"IF ISNULL(dbreader("fksubgroup")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fksubgroup") & "'"IF ISNULL(dbreader("orderid")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("orderid") & "'"IF ISNULL(dbreader("fkordertype")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkordertype") & "'"IF ISNULL(dbreader("date")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("date") & "'"IF ISNULL(dbreader("orderbegindate")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("orderbegindate") & "'"IF ISNULL(dbreader("orderenddate")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("orderenddate") & "'"IF ISNULL(dbreader("fknetwork")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fknetwork") & "'"IF ISNULL(dbreader("estimatenumber")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("estimatenumber") & "'"IF ISNULL(dbreader("flight")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("flight") & "'"IF ISNULL(dbreader("donovan")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("donovan") & "'"IF ISNULL(dbreader("fkadvertiser")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkadvertiser") & "'"IF ISNULL(dbreader("fkagency")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkagency") & "'"IF ISNULL(dbreader("fktrafficsystem")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fktrafficsystem") & "'"IF ISNULL(dbreader("agencycommission")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("agencycommission") & "'"IF ISNULL(dbreader("buyer")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("buyer") & "'"IF ISNULL(dbreader("fkclient")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkclient") & "'"IF ISNULL(dbreader("repcommission")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("repcommission") & "'"IF ISNULL(dbreader("fkproducttype")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkproducttype") & "'"IF ISNULL(dbreader("product")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("product") & "'"IF ISNULL(dbreader("buydesc")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("buydesc") & "'"IF ISNULL(dbreader("fkae")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkae") & "'"IF ISNULL(dbreader("fkae2")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("fkae2") & "'"IF ISNULL(dbreader("aesplit")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("aesplit") & "'"IF ISNULL(dbreader("natsplit")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("natsplit") & "'"IF ISNULL(dbreader("pkid")) Then SQL = SQL & ", NULL" Else SQL = SQL & ", '" & dbreader("pkid") & "'" SQL = SQL & ")"Peter LarssonHelsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-06 : 02:31:20
|
| Use stored procedure with parameters. Avoid concatenated SQLMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
SQL Query