inserting date

[truck]

hello guys am a beginner to vb.net am trying to make an application for my videos

i have a problem to insert birthdate of the actor to the sql server database

the birthdate field in sql server is set to date not datetime

let's say birthdate = 02-13-1980
when i click the save button am getting the error

incorrect syntax near '02-13-1980'

this is my class code


ImportsSystem.Data.Sql
ImportsSystem.Data.SqlClient

PublicClass SQL_Control

PublicSQLConAsNewSqlConnectionWith{.ConnectionString="Server=TRUCK-PC\TRUCK;Database=Videos;User=sa;Pwd=123456;"}
PublicSQLCmdAsSqlCommand
Public SQLDA AsSqlDataAdapter
PublicSQLDatasetAsDataSet


PublicFunctionHasConnection()AsBoolean

Try

SQLCon.Open()

SQLCon.Close()
ReturnTrue

Catch ex AsException

MsgBox(ex.Message)
ReturnFalse
EndTry

EndFunction

PublicSubRunQuery(QueryAsString)

Try

SQLCon.Open()

SQLCmd=NewSqlCommand(Query,SQLCon)

SQLDA =NewSqlDataAdapter(SQLCmd)
SQLDataset=NewDataSet
SQLDA.Fill(SQLDataset)

SQLCon.Close()

Catch ex AsException

MsgBox(ex.Message)

IfSQLCon.State=ConnectionState.OpenThen

SQLCon.Close()

EndIf

EndTry

EndSub


PublicSubAddNewActor(S_ActorName AsString, S_ActorNationality AsString, S_ActorBirthDate AsDate)

Try
Dim strInsert AsString="INSERT INTO Videos (Actor_Name,Actor_Nationality,Actor_Birth_Date) "& _
"VALUES ("& _
"'"& S_ActorName &"',"& _
"'"& S_ActorNationality &"',"& _
"'"& S_ActorBirthDate &"'"

MsgBox(strInsert)

SQLCon.Open()
SQLCmd=NewSqlCommand(strInsert,SQLCon)

SQLCmd.ExecuteNonQuery()

SQLCon.Close()

Catch ex AsException

MsgBox(ex.Message)

EndTry

EndSub
EndClass




this is my form code

ImportsSystem.Data.SqlClient

PublicClass F2_Actors

Dim SQL AsNew SQL_Control

PrivateSubButton1_Click(sender AsObject, e AsEventArgs)HandlesButton1.Click

SQL.RunQuery("SELECT * FROM tbl_actors WHERE tbl_actors.Actor_Name = '"&ActorName.Text&"' ")

If SQL.SQLDataset.Tables(0).Rows.Count>0Then

MsgBox("Actor already exist",MsgBoxStyle.Critical,"Videos Database - Adding New Actor")

Else

AddActor()

EndIf
EndSub

PublicSubAddActor()

SQL.AddNewActor(ActorName.Text,ActorNationality.Text,ActorBirthDate.Text)

EndSub
EndClass



and thanks for your help

 

[jmcilhinney]

For future reference, attaching a whole project should be a last resort. It's a waste of our time to download it, extract it, open it and then try to work out where this issue might be when you could simply post a few lines of code that show exactly.

The problem is almost certainly that you are using a String when you should be using a Date. You should be using parameters to insert any variable values into your SQL code and you should be using a Date to set the value of that parameter. Follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. The examples I include even use Dates.

 

[truck]

Jmcilhinney i tried what you wrote in your blog but my problem is the addind code is in a class and then i call it from the specified form so can you lead me please

 

[jmcilhinney]

Jmcilhinney i tried what you wrote in your blog but my problem is the addind code is in a class and then i call it from the specified form so can you lead me please

That's quite irrelevant. ADO.NET is ADO.NET. In fact, well-written ADO.NET code should never be in a form to start with. Adding a parameter to a command is the same no matter where it is though.

 

[truck]

Ok sir i'll give a try thanks anyway

 

[jwcoleman87]

Try using the DateTime object instead of a string in your vb code

 

[jmcilhinney]

Try using the DateTime object instead of a string in your vb code

I wish I'd said that. Oh, I did.

 

[truck]

Thank you all for your help
I did it with parameters and everything is done