Rob Rob -4 years ago 131
Vb.net Question

ASP.NET/VB date works on localhost but not remoteserver

I'm having alot of trouble trying to get the date from a DateTime picker to save to my database. It works fine when run from Localhost, but when running it on the public server it doesn't.
Originally i used the cDate function, plus also tried Convert.ToDateTime. But on the server it now gives me an error "String was not recognized as a valid DateTime."

I researched and found that its best to used the DateTime.TryParse function. After finding an example i changed it for my project and now im getting the error "Must declare the scalar variable "@articledate"."

When i debug the project, the value added into the "result1" DateTime is correct. But it still throws the error at ExecuteNonQuery?

If it makes a difference, the main server is hosted with GoDaddy, Localhost is my PC.

Any help or advice would be greatly appreciated.

cnSQL1 = New SqlConnection(MSSQL.cRemoteConnectionString)
cnSQL1.Open()
sSQL = "SELECT NewsID FROM News WHERE Season = @season AND Heading = @heading AND ArticleDate = @articledate AND Author = @author AND Club = @club AND State = @state AND AddedDate = @addeddate AND AddedBy = @addedby AND Release = @release"
Dim com1 As New SqlCommand(sSQL)
com1.Connection = cnSQL1
com1.Parameters.AddWithValue("@season", General.sSeason)
com1.Parameters.AddWithValue("@heading", General.UppercaseFirstLetter(txtHeading.Text))
Dim result1 As DateTime
com.Parameters.AddWithValue("@articledate", DateTime.TryParseExact(txtArticleDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, result1))
'com1.Parameters.AddWithValue("@articledate", Convert.ToDateTime(txtArticleDate.Text))
com1.Parameters.AddWithValue("@author", txtAuthor.Text)
com1.Parameters.AddWithValue("@club", ddlClub.SelectedItem.Value)
com1.Parameters.AddWithValue("@state", dsState.Tables(0).Rows(0).Item(0))
com1.Parameters.AddWithValue("@addeddate", Date.Today)
com1.Parameters.AddWithValue("@addedby", Session("UserID"))
com1.Parameters.AddWithValue("@updatedate", Date.Today)
com1.Parameters.AddWithValue("@updateby", Session("UserID"))
com1.Parameters.AddWithValue("@release", s)
com1.ExecuteNonQuery()

Answer Source

The error "Must declare the scalar variable "@articledate" is caused by a typo. When you add the parameter you should use the SqlCommand variable named com1 not the variable com.

But after this you have a more critical error. All the TryParse methods return booleans. The converted date is written in the last parameter passed to TryParse.

In this way you are passing a boolean to AddWithValue and this method takes whatever you put in the value parameter. It has no way to know that you want a date, so it happily complies with your request.

You should do (Notice that the SqlCommand to use is com1 not com)

Dim result1 As DateTime
if DateTime.TryParseExact(txtArticleDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, result1) Then
    com1.Parameters.AddWithValue("@articledate", resul1)
Else
    .....

In general it is better to avoid AddWithValue exactly because it takes whatever you pass to it. The preferred way is with

if DateTime.TryParseExact(txtArticleDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, result1) Then
    com1.Parameters.Add("@articledate", SqlDbType.Datetime).Value = result1
    com1.Parameters.AddWithValue("@season", General.sSeason)
    com1.Parameters.AddWithValue("@heading", General.UppercaseFirstLetter(txtHeading.Text))

    com1.Parameters.AddWithValue("@author", txtAuthor.Text)
    com1.Parameters.AddWithValue("@club", ddlClub.SelectedItem.Value)
    com1.Parameters.AddWithValue("@state", dsState.Tables(0).Rows(0).Item(0))
    com1.Parameters.AddWithValue("@addeddate", Date.Today)
    com1.Parameters.AddWithValue("@addedby", Session("UserID"))
    ' This is not used by the query, commented out
    ' com1.Parameters.AddWithValue("@updatedate", Date.Today)
    ' This is not used by the query, commented out
    ' com1.Parameters.AddWithValue("@updateby", Session("UserID"))
    com1.Parameters.AddWithValue("@release", s)
    com1.ExecuteNonQuery()
Else
    MessageBox.Show("Date is not valid")
End If

I have also removed two parameters that are not used in the query above (though they should not cause the error message above)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download