?
Solved

Syntax error converting datetime from character string

Posted on 2004-10-17
6
Medium Priority
?
261 Views
Last Modified: 2010-04-23
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
Comment
Question by:Donnie4572
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
Timbo87 earned 2000 total points
ID: 12333696
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
 
LVL 12

Author Comment

by:Donnie4572
ID: 12334141
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
 
LVL 15

Expert Comment

by:Timbo87
ID: 12334162
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Author Comment

by:Donnie4572
ID: 12334213
Yes.
Only I have the from where

sql = "insert into mytable(myid, mydate) values (@myid, @mydate) FROM mytable WHERE (dataID = @@IDENTITY)"
0
 
LVL 15

Expert Comment

by:Timbo87
ID: 12334396
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
 
LVL 12

Author Comment

by:Donnie4572
ID: 12334599
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question