MS Access and VB.NET, null values

[CraigEdel]

Hi Everyone. I am having a problem that hopefully someone can help me with. I am creating an ASP.NET form using VB.NET, connected to an access database. It is just your basic form, with text boxes, radio boxes, etc. My problem is, in access there are certain data types, for example, Number, Text, Date/Time, etc. The text fields have an option to Allow Zero Length strings, or null values.

If I do not fill out these fields on the form that link to these fields in the database, the form works, but if I do not fill out something that is Text or a Date/Time, VB.NET crashes and I Get the error "Data type mismatch in criteria expression .NET". There are optional fields on this form, so I cannot figure out how to get around leaving them blank, since I have one SQL statement that inserts everything into the database. Any ideas?
Thanks a lot.

 

[wyrd]

If Im understanding your problem right, its probably crashing on the DateTime because youre trying to insert a blank string.

Regardless of that, I would build the SQL statement dynamically based on what the user entered. If the user didnt enter a value for a field, then you need to check for that and put NULL in its place. Maybe something like this;

If (txtBox.Text.Length <= 0) {
// (txtBox.Text == "") also works, but I like using Length.
    txtBox.Text = "NULL";
}

Then when you come to inserting values using the .Text property (or whatever) it should insert the proper NULL rather then a blank string.

 

[CraigEdel]

Thanks a lot, I think that will do the trick.

 

[CraigEdel]

Does C# and VB.NET have different NULL values? I am still receiving the same error, is there something I need to maybe do on the database side as well? I am using VB.NET, I will look into inserting NULL values with it, but for now, any other ideas?

 

[wyrd]

It has nothing to do with C# or VB.NET as all youre doing is using a string value, this is a database thing and all databases should handle NULL the same way.

It would help if you could give us your full table details (each column with constraints, etc) and also if possible the SQL statement youre using.

 

[CraigEdel]

The database fields I am worrying about are Dose1, Dose2, Dose3, and Dose4, which all have the data type Number, Field Size Single, Decimal places 1, Required No, Indexed No, rest of the General Access properties are not filled out.

I have pasted line by line my code for the SQL statement.

 strSQL = "INSERT INTO CBP (ID, LastName, FirstName, MI, MED1, Dose1, MED2, Dose2, MED3, Dose3, DateLastMeds, MED4, Dose4, TimeLastMeds, V_Date, BPhour) " _

& "VALUES (" & Request.Form("txtID") & ", " & Request.Form("txtLN") _

& ", " & Request.Form("txtFN") & ", " & Request.Form("txtMI") _

& ", " & totalMed1 & ", " & dose1 & ", " & totalMed2 _

 & ", " & dose2 & ", " & totalMed3 & ", " & dose3 & ", " & lastmeddate _

  & ", " & totalMed4 & ", " & dose4 & ", " & lastmedtime _

  & ", " & firstBPTakenDate & ", " & firstBPTakenTime & ");"

dose1, dose2, dose3, and dose4 are strings that come together when the user enters data into text boxes, for e.g., 3 text boxes with one number in each, if they put 1 in them, it joins to create 11.1. dose1 is required, the rest are not.

Any ideas?