CraigLazar
asked on
Null value issue writing to my database
Hi,
I am looking at someone code and need to fix an Null issue. It is really simple but i have not worked in .NET in awhile.
I have a function that takes the values that have been entered into a grid are then written to my sql database.
i need to make sure that if there is a null value, to set it to "" or not null before writing it away
So in belows code snippet, i need to test the PortfolioDescription before getting written tot eh database
thanks allot :)
below is the code
can i use the isDBNull function?
Dim da As New OdbcDataAdapter
Dim cmd As New OdbcCommand
Dim cmd_del As New OdbcCommand
Dim cmd_ins As New OdbcCommand
Dim cn As New OdbcConnection
Dim pr, pr2, pr3 As OdbcParameter
With cn
.ConnectionString = CON_STR
Try
.Open()
Catch ex As Exception
ErrorRoutine(ex)
End Try
End With
With cmd
.Connection = cn
.CommandText = " UPDATE ClientGroupPortfolio" & _
" SET PortfolioCode=?,PortDescri ption=?,Ex Report=?" & _
" WHERE GroupID=? AND PortfolioCode=?"
'.Parameters.Add("@GroupID ", OdbcType.VarChar, 4, "GroupID")
.Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
.Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
.Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")
pr = .Parameters.Add("@GroupID" , OdbcType.VarChar, 4, "GroupID")
pr.SourceVersion = DataRowVersion.Original
pr2 = .Parameters.Add("@Portfoli oCode", OdbcType.VarChar, 6, "PortfolioCode")
pr2.SourceVersion = DataRowVersion.Original
End With
With cmd_ins
.Connection = cn
.CommandText = " INSERT INTO ClientGroupPortfolio" & _
" (GroupID,PortfolioCode,Por tDescripti on,ExRepor t)" & _
" VALUES(?,?,?,?)"
.Parameters.Add("@GroupID" , OdbcType.VarChar, 4, "GroupId")
.Parameters.Add("@Portfoli oCode", OdbcType.VarChar, 6, "PortfolioCode")
.Parameters.Add("@PortDesc ription", OdbcType.Char, 50, "PortDescription")
.Parameters.Add("@ExReport ", OdbcType.Bit, 1, "ExReport")
End With
da.UpdateCommand = cmd
da.DeleteCommand = cmd_del
da.InsertCommand = cmd_ins
Try
da.Update(dtCGP)
MessageBox.Show("Updates Completed!", "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information )
Exit Sub
Catch ex As Exception
MessageBox.Show("Update Failed! " & ex.Message, "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Exclamation )
'ErrorRoutine(ex)
End Try
cn.Close()
I am looking at someone code and need to fix an Null issue. It is really simple but i have not worked in .NET in awhile.
I have a function that takes the values that have been entered into a grid are then written to my sql database.
i need to make sure that if there is a null value, to set it to "" or not null before writing it away
So in belows code snippet, i need to test the PortfolioDescription before getting written tot eh database
thanks allot :)
below is the code
can i use the isDBNull function?
Dim da As New OdbcDataAdapter
Dim cmd As New OdbcCommand
Dim cmd_del As New OdbcCommand
Dim cmd_ins As New OdbcCommand
Dim cn As New OdbcConnection
Dim pr, pr2, pr3 As OdbcParameter
With cn
.ConnectionString = CON_STR
Try
.Open()
Catch ex As Exception
ErrorRoutine(ex)
End Try
End With
With cmd
.Connection = cn
.CommandText = " UPDATE ClientGroupPortfolio" & _
" SET PortfolioCode=?,PortDescri
" WHERE GroupID=? AND PortfolioCode=?"
'.Parameters.Add("@GroupID
.Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
.Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
.Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")
pr = .Parameters.Add("@GroupID"
pr.SourceVersion = DataRowVersion.Original
pr2 = .Parameters.Add("@Portfoli
pr2.SourceVersion = DataRowVersion.Original
End With
With cmd_ins
.Connection = cn
.CommandText = " INSERT INTO ClientGroupPortfolio" & _
" (GroupID,PortfolioCode,Por
" VALUES(?,?,?,?)"
.Parameters.Add("@GroupID"
.Parameters.Add("@Portfoli
.Parameters.Add("@PortDesc
.Parameters.Add("@ExReport
End With
da.UpdateCommand = cmd
da.DeleteCommand = cmd_del
da.InsertCommand = cmd_ins
Try
da.Update(dtCGP)
MessageBox.Show("Updates Completed!", "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information
Exit Sub
Catch ex As Exception
MessageBox.Show("Update Failed! " & ex.Message, "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Exclamation
'ErrorRoutine(ex)
End Try
cn.Close()
ASKER
hi
forgive my ignorance but i am battling in the code above to figure out where to put ur code. I need to make sure the Productdescription is not NULL
so would i put this statement where?
thanks
forgive my ignorance but i am battling in the code above to figure out where to put ur code. I need to make sure the Productdescription is not NULL
so would i put this statement where?
thanks
With cn
.ConnectionString = CON_STR
Try
.Open()
Catch ex As Exception
ErrorRoutine(ex)
End Try
End With
' put your code here
' put your code here
With cmd
.Connection = cn
.CommandText = " UPDATE ClientGroupPortfolio" & _
" SET PortfolioCode=?,PortDescri ption=?,Ex Report=?" & _
" WHERE GroupID=? AND PortfolioCode=?"
'.Parameters.Add("@GroupID ", OdbcType.VarChar, 4, "GroupID") ' These words are in '' but I think they should be variables. right????????????? => 'GroupID'
.Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
.Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
.Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")
pr = .Parameters.Add("@GroupID" , OdbcType.VarChar, 4, "GroupID")
pr.SourceVersion = DataRowVersion.Original
pr2 = .Parameters.Add("@Portfoli oCode", OdbcType.VarChar, 6, "PortfolioCode")
pr2.SourceVersion = DataRowVersion.Original
End With
.ConnectionString = CON_STR
Try
.Open()
Catch ex As Exception
ErrorRoutine(ex)
End Try
End With
' put your code here
' put your code here
With cmd
.Connection = cn
.CommandText = " UPDATE ClientGroupPortfolio" & _
" SET PortfolioCode=?,PortDescri
" WHERE GroupID=? AND PortfolioCode=?"
'.Parameters.Add("@GroupID
.Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
.Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
.Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")
pr = .Parameters.Add("@GroupID"
pr.SourceVersion = DataRowVersion.Original
pr2 = .Parameters.Add("@Portfoli
pr2.SourceVersion = DataRowVersion.Original
End With
ASKER
well the code was written round a grid, so the values are comming in of a datagrid and not text boxes on a form
which i can see now its datasource is a datatable.
Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
is the @PDesc holding the actual value?
which i can see now its datasource is a datatable.
Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
is the @PDesc holding the actual value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok thanks so much
thats a great help
cheers
thats a great help
cheers
if GroupID = nothing then
groupID = ""