Insert into Access db and optional fields

[hockeylvr]

I have created an asp.net form page which inserts the inputted data into an Access db. I have some form fields that are not required, but, when left blank, I am getting an error message stating that the values are missing.
I have them set up in my Access table as not required. Has anyone else had/resolved this issue? The non-required field I am currently working on is @red,"txtRed"

Sub SubmitProject(ByVal s As Object, ByVal e As EventArgs)

objCmd = New Data.OleDb.OleDbCommand("INSERT INTO tblTemp (owner, manager, projtype, pf, status, <b>@red</b>, @volume,@cutin)", objConn)

objCmd.Parameters.AddWithValue("@owner", ddlOwner.SelectedItem.Value)
objCmd.Parameters.AddWithValue("@manager", ddlMgr.SelectedItem.Value)
objCmd.Parameters.AddWithValue("@projtype", ddlProjType.SelectedItem.Value)
objCmd.Parameters.AddWithValue("@pf", ddlPF.SelectedItem.Value)
objCmd.Parameters.AddWithValue("@status", ddlStatus.SelectedItem.Value)
<b> objCmd.Parameters.AddWithValue("@red", txtRed.Text)</b>

objCmd.Parameters.AddWithValue("@volume", txtVol.Text)
objCmd.Parameters.AddWithValue("@cutin", Convert.ToDateTime(txtCutIn.Text))


objConn.Close()
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
Response.Redirect("Default2.aspx")

Thanks for any help - also, I seem to be coming across what I'm sure are very simple questions like these. Could anyone recommend a very "beginners" book on asp.net? I currently have a Sitepoint and a Microsoft book but they don't address some of what I think are the basics like this.

Toni

 

[ca8msm]

Just check to see if the TextBox is blank before adding the parameter


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[hockeylvr]

I added this line:

If Not txtRed.Text = "" Then
objCmd.Parameters.AddWithValue("@red", txtRed.Text)
End If
...And am getting this error message:

No value given for one or more required parameters.

Do I need to change data in the "INSERT" statement?

Thanks

 

[IT4EVR]

Actually I think you can do this:

If objCmd.Parameters("@red") Is Null
     objCmd.Parameters.AddWithValue("@red",String.Empty)
Else
     objCmd.Parameters.AddWithValue("@red", txtRed.Text)
End If

Why are you passing in the bold into the parameter?

 

[ca8msm]

Yes, you'll need to amend your SQL query to allow for the potential of a value being Null. Ideally, you should move this to a Stored Procedure anyway which will make it easier as well as brining other benefits.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[hockeylvr]

Thanks, I think I'm getting closer. I'm using VWD and when I use "Null" it is telling me it is no longer supported, use "System.DBNull" instead. If I do that, I get "DBNull is a type in "System" and cannot be used as an expression"
Once I get this part working I will dive into a stored procedure.

Thanks!

 

[ca8msm]

Actually I think you can do this:

However, with that method you are passing an empty string which is different to a null value.



____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[IT4EVR]

Good point and if you really want to get into semantics, there is no such thing as a "null value". Null represents a case where there is no value or the value is unknown.

I think I was thinking in terms of Oracle, where an empty string resolves to null.

 

[IT4EVR]

And some argue you shouldn't allow nulls into your tables. That you should always specify a default value. The idea is that checking for nulls complicates your queries and stored procedures and also makes tables less efficient.

 

[ca8msm]

And some argue you shouldn't allow nulls into your tables. That you should always specify a default value. The idea is that checking for nulls complicates your queries and stored procedures and also makes tables less efficient.

I'd disagree with that philosophy as if something doesn't exist it is null or nothing (depending on how you want you want to refer to something that doesn't exist). If you give something a default value, how can you distinguish between something that doesn't exist and something that does exist but happens to have the same value that you chose as a default? The simple answer is you can't yet you should be able to because they are different.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[hockeylvr]

Got it! Thanks for all of the help.

objCmd.Parameters.AddWithValue("@red", (IIf(txtRed.Text = "", System.DBNull.Value, txtRed.Text)))