Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Syntax error converting datetime from character string

I have web app and can't get past this error. I have datagrid with select column and the event fires this code which should place data from datagrid to sql database.
This code is working for all columns except the date time column.

Dim dgmyid As Integer
Dim dgmydate As String
Dim sql As String

dgmyid = mydatagrid.SelectedItem.Cells(1).Text
dgmydate = mydatagrid.SelectedItem.Cells(2).Text

SqlConnection1.Open()

Dim myinsertCommand As SqlCommand = New SqlCommand

myinsertCommand.Connection = SqlConnection1

sql = "insert into mytable(myid, mydate) values (@myid, @mydate); select myid, mydate from mytable where (dataID = @@IDENTITY)"

myinsertCommand.CommandText = sql

myinsertCommand.Parameters.Add("@myID", System.Data.SqlDbType.VarChar).Value = dgmyid
myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = dgmydate

myinsertCommand.ExecuteNonQuery()

SqlConnection1.Close()

Thanks for your help,
Donnie

 

       
0
Donnie4572
Asked:
Donnie4572
  • 3
  • 3
1 Solution
 
Timbo87Commented:
Try changing this line:

myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = dgmydate

To

myinsertCommand.Parameters.Add("@myDate", System.Data.SqlDbType.DateTime).Value = DateTime.Parse(dgmydate)

One question though, why do you have a SELECT statement after the INSERT when you're executing a non-query?
0
 
Donnie4572Author Commented:
This works! Very Good Timbo87! I have worked all day on this. (new to sql and vb)

I assumed that the ExecuteNonQuery needed the select statement. Since your post I have tried the above code without the select and got incorrect syntax near from.
Could you post any easier way to write this?
sql = "insert into mytable(myid, mydate) values (@myid, @mydate); select myid, mydate from mytable where (dataID = @@IDENTITY)"

Thanks,
0
 
Timbo87Commented:
Simply removing that select shouldn't cause a syntax error. Does your sql string look like this?

sql = "insert into mytable(myid, mydate) values (@myid, @mydate)"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Donnie4572Author Commented:
Yes.
Only I have the from where

sql = "insert into mytable(myid, mydate) values (@myid, @mydate) FROM mytable WHERE (dataID = @@IDENTITY)"
0
 
Timbo87Commented:
I don't believe that's a valid SQL query. I think this is what you want:

sql = "insert into mytable(myid, mydate) values (@myid, @mydate)"

0
 
Donnie4572Author Commented:
You are right again. I removed the "select from and where"  it works well. My way was working but thanks for showing me the right way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now