DataSource Controls :: Right Syntax For Datetime In Database?
Feb 13, 2010
working with sql server 2005/ visual studio 2005 asp.net c# i just wanted to know ... to insert a date to the database in a field that gets "dateTime" i need to insert the origin syntax. what is the right syntax for the datetime in the database? and to invent for users to insert a wrong sytax ... wich validations i need to put on the field? same question on the type "image" in database it gets like a jpg file? how do i insert an image to the database? If i have a textbox that contain a number ... and i want to ask somthing about the number. how do i do it ? .... like i now that i need to convert it to int i tried this way but it didnt work:
I keep getting errors when I try to convert. I have a date row that when my page loads it displays as
1/28/2010 12:00:00 AM.
I have my default value set to '00:00:00" for it. I'm trying to convert it to 24 hour format so that it will read out as 00:00:00 initially. SQL Statement(The one that is working)
SELECT user_id, bpi_title, HoR, Date, Duration FROM tblViewLog2 JOIN tblBPIModules on tblViewLog2.module_id = tblBPIModules.id
I assume that I need to modify my SQL statement, but I'm having a hard time with the exact syntax. I'm trying to figure out exactly what I need to do with the information below in order to get what I want.
14 or 114 24 hour time with milliseconds. HH:mm:ss:fff 21:00:00:000
I am beginner to sql database and asp.net.I want to fetch datetime from sql database and display it in textbox.I have wrote one stored procedure.The Procedure is
declare @o_Output varchar(30) SET @o_Output = getdate() select @o_Output
Using the above procedure i'm getting current date and time.but my problem is i am hitting ql database while page loading itself. if i am hitting db on 12.40pm means, the textbox showing always the sametime only. I am trying to insert or update some data's into db that time only, i am hitting the db.The textbox datetime(12.50pm) has been changed that time alone. i want to update datetime to textbox at every minutes or seconds.
I have a record created date stored as" 2010-05-16 15:44:18.700" in sql server database. When I read this date it gets converted to "5/16/2010 3:44:18 PM" . How should I read it so that I don't loose the ending '700' value so that it gets written exactly to another database.Code for reading the record:
String myConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); SqlConnection con = new SqlConnection(myConn); SqlCommand cmd = new SqlCommand();[code].....
I am selecting time from dropdownlist. and want to convert it in datetime and store it in database. When I am converting it to Datetime using following statement.
On UI Ajax calendar control is used to allow the user Expiry Date as shown below. As this is one of the controls in search, when the Search button is pressed using SQL statement (copied below) data is retrieved from ExpiryDate column of the table. The table creator declared the column type as string and the dates are saved as single digits(Example: 2/2/10 sometimes and sometime as double digits (Example: 02/02/10) I like to know the best practice in retrieving both type of strings from database table. Calendar control
[Code]....
SQL(part of stored proc)
[Code]....
with in button click event stored procedure is called to return search results.
i want to bind my table to the datagridview in VB.net windows form (not in asp.net). but when i try to bind and run the applicatiion i got the following error "syntax error converting datetime from character string". my table contains two date filed"pdate","date" which contains the date in the followingg format"2010-02-01 00:00:00.000 i want to bind this date fileds to datagrid view.
Dim connStr As String = " Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=W:PatioOrderApp_DataPatio.mdb" Dim con As New OleDb.OleDbConnection(connStr) Dim instr As String = " INSERT INTO tOrders([first_name],[last_name],[order],[comment]) Valuse(?,?,?,?)" If IsValid Then Using con Using cmd As New OleDb.OleDbCommand(instr, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("first_name", DBNull.Value) cmd.Parameters.AddWithValue("last_name", DBNull.Value) cmd.Parameters.AddWithValue("order", DBNull.Value) cmd.Parameters.AddWithValue("comment", menuDrinks.SelectedItem.ToString) con.Open() cmd.ExecuteNonQuery() End Using End Using con.Close() End If
I have this Insert statement working fine: [Code]....
It inserts a new record into a single table. The PK is a field named QID.what the equivalent Update code would be? I'm not sure of the syntax for the Update values (old and new) and Where clause.I'm assuming something beginning with: existingQ.Update
here is my update query syntax.my problem is.if i update only logo1 and logo3 with null logo2 and img null then my query should be update the other record.just dont update null value record but other should be update.but below my query can not update any record.
UPDATE tbitem set itemname= @itemname,note=@note, price=@price,qty=@qty,logo1=@logo1, logo2=@logo2,logo3=@logo3,img=@img where id =@id and @logo1 is null and @logo2 is null and @logo3 is null and @img is null
I hv Writte nA sql Query in whic hi want to use an Inner join as well as THe left OUter Join in it ..i hv written it in the sql server it executes properly.but when i run the same code ..from my application .it starts gving the Error Incorrect Syntax Near Keyword INNER..
I am just not able to get the syntax right for the below code,
Dim HeaderCount As Integer = readerXML.HeaderCount() Dim NewTable As String NewTable = "CREATE TABLE " + tbCustomerFileName.Text + " (" + " RowId smallint" + " IDENTITY(1,1)" + " PRIMARY KEY CLUSTERED," For I = 0 To [HeaderCount] NewTable = NewTable + readerXML.Item(I) + " DEFAULT 'New Position - title not formalized yet'," + " varchar(50) NOT NULL," Next [I] + ")" ...this part gives me an error ssaying incorrect syntax.
I am trying to upload an excel file and based on the number of headers in the file, I want to generate columns, however I am just not able to get this going because i am getting syntax error, the main problem being I am not able to put the brackets at the right place.
Incorrect syntax near '>'. I made a listview with some dropdownmenu and selectboxes to select from untill the query shows in the listview.
Now the query goes well and shows the first page of 4 rows as I wanted it.
The pager shows 5 pages and if I a want to click on the next page I get this message.
Incorrect syntax near '>'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '>'.
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:AvcilarEvleri %>" SelectCommand="SELECT * FROM Tabel1 WHERE ((([Regio_Object] = @Regio_Object) AND ([Plaats_Object] = @Plaats_Object) AND ([Wijk_Object] = @Wijk_Object) AND (Prijsklasse => @Prijsklasse1) AND (Prijsklasse <= @Prijsklasse2) AND (Prijsklasse => @Prijsklasse3) AND (Prijsklasse <= @Prijsklasse4 AND (Oppervlakte_Object => @Oppervlakte_Object1) AND (Oppervlakte_Object <= @Oppervlakte_Object2)) AND ((Type_Object = @Type_Object1) OR (Type_Object = @Type_Object2) OR (Type_Object = @Type_Object3)))"> <SelectParameters> <asp:ControlParameter ControlID="DropSehir1" Name="Regio_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropIl1" Name="Plaats_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropDownList5" Name="Wijk_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat1" Name="Prijsklasse1" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat2" Name="Prijsklasse2" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat3" Name="Prijsklasse3" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat4" Name="Prijsklasse4" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropMetre1" Name="Oppervlakte_Object1" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropMetre2" Name="Oppervlakte_Object2" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object2" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object3" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
And the code;
Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Panel1.Visible = False Panel4.Visible = False Panel5.Visible = False Panel6.Visible = False Panel7.Visible = False Panel8.Visible = False Panel9.Visible = False Panel10.Visible = False Panel2.Visible = True Panel3.Visible = True Dim strchklist As String = "" Dim li As ListItem For Each li In CheckBoxList1.Items If li.Selected Then strchklist += ",'" & li.Value & "'" End If Next If strchklist = "" Then Response.Write("No item Selected") End If SqlDataSource4.SelectCommand = "SELECT * FROM Tabel1 WHERE ([Regio_Object] = @Regio_Object) AND (Plaats_Object = @Plaats_Object) AND (Wijk_Object = @Wijk_Object) AND (Prijsklasse >= @Prijsklasse3) AND (Prijsklasse <= @Prijsklasse4) AND (Oppervlakte_Object >= @Oppervlakte_Object1) AND (Oppervlakte_Object <= @Oppervlakte_Object2) AND Kamers_Object IN (" & strchklist.Substring(1) & ")" SqlDataSource4.DataBind() End Sub
I am sure it has something to do with the query because and with the checkboxes.
I'm using the following code to select a number of fields from a Table, this works perfectly, however I actually want to select from a stored procedure not a table and im not sure what the correct syntax is,
I have a problem with my SQL statement that only needs to show future dates in the Gridview.I use vb, vs 2005.I want a DateTime Field(TEndDate) to act as a control field in that only future dates (WHERE TEndDate >= Now.Date.Date) should be returned.It asks for my paramaterized value when I'm building the query.I'm unsure about the source I should select for the query.(The options are: None/Control/Cookie/Form/Profile/Querystring/Session)I'm losing my mind with this!Here is my code:
Where can I find documentation on the syntax that is expected for LinqDataSource.Select? It seems to be different from what I would do with Linq inline in C# code. For example, concatenateing something in Linq I would normally just do this in the projection:
new { FullName = (person.FirstName + " " + person.LastName ) } However, with a LinqDataSource the only way I've found to do this is like this: new ( String.Concat(person.FirstName, " ", person.LastName) as FullName)
I literally spent hours trying to figure this out, and only succeeded when I found a blog where someone gave this verbatim as an example. So I still don't understand why the syntax is different, the braces versus parens, aliasing, Concat operators. Next time I do this I want a better understanding so I am not playing this guessing game. It's like being handed a compiler, and trying to write a program when you don't know what language the compiler compiles.1. Are there different "flavors" of Linq? Like a VB Linq, and C# Linq?2. If so, is the LinqDataSource using a VB flavor of Linq?3. Really, what is really important to me, where can I find documentation on this particular falvor of Linq that the LinqDataSource expects? The documentation for the LinqDataSource itself gives nothing more than extremely simplistic examples.
I get the following error on the e.Command.Parameters("@latestRowId").Value line. It was working, but now not and I'm not sure why? Conversion from type 'DBNull' to type 'Integer' is not valid.
May I know in SQL Server, what is the similar syntax for %TYPE in Oracle, i.e. the variable defined will follow the data type defined in the database column?
Can someone please tell me what normally causes this error message (stack trace at bottom of this post)? I can't even tell what line or what page is producing the error.
Following are the relevant (I think) 2 code procedures...the Update command below seems to "work", because later I see that the image is updated---but I can't immediately see that: I get the above error message.
'FormView.ItemUpdating Event Protected Sub AdvertisementForm_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles AdvertisementForm.ItemUpdating ' Get the connection string from Web.config. Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabaseConnectionString").ToString()) ' Create a command object. Dim cmd As New SqlCommand() ' Assign the connection to the command. cmd.Connection = conn ' Set the command text ' SQL statement or the name of the stored procedure. cmd.CommandText = "UPDATE Advertisements SET UserName = @UserName, Age = @Age, Avatar = ISNULL(@Avatar, Avatar) WHERE UserId = @UserId" ' Set the command type ' CommandType.Text for ordinary SQL statements; cmd.CommandType = CommandType.Text ' Get the person ID, first name and last name from the ' EditItemTemplate of the FormView control. Dim strUserId As String = DirectCast(AdvertisementForm.Row.FindControl("UserIdTextBox"), TextBox).Text Dim strUserName As String = DirectCast(AdvertisementForm.Row.FindControl("UserNameTextBox"), TextBox).Text Dim strAge As String = DirectCast(AdvertisementForm.Row.FindControl("AgeTextBox"), TextBox).Text ' Append the parameters to the SqlCommand and set values. cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = Guid.Parse(strUserId) cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = strUserName cmd.Parameters.Add("@Age", SqlDbType.NChar).Value = strAge ' Find the FileUpload control in the EditItemTemplate of ' the FormView control. Dim uploadAvatar As FileUpload = DirectCast(AdvertisementForm.FindControl("uploadAvatar"), FileUpload) If uploadAvatar.HasFile Then ' Append the Picture parameter to the SqlCommand. ' If a picture is specified, set the parameter with ' the value of bytes in the specified picture file. cmd.Parameters.Add("@Avatar", SqlDbType.VarBinary).Value = uploadAvatar.FileBytes Else ' Append the Picture parameter to the SqlCommand. ' If no picture is specified, set the parameter's ' value to NULL. cmd.Parameters.Add("@Avatar", SqlDbType.VarBinary).Value = DBNull.Value End If ' Open the connection. conn.Open() ' Execute the command. cmd.ExecuteNonQuery() End Using ' Switch FormView control to the ReadOnly display mode. AdvertisementForm.ChangeMode(FormViewMode.ReadOnly) ' Rebind the FormView control to show data after updating. BindFormView() End Sub Private Sub BindFormView() ' Get the connection string from Web.config. Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabaseConnectionString").ToString()) ' Create a DataSet object. Dim dsPerson As New DataSet(). ' Create a SELECT query. Dim strSelectCmd As String = "SELECT UserId, Avatar FROM Advertisements" ' Create a SqlDataAdapter object Dim da As New SqlDataAdapter(strSelectCmd, conn) ' Open the connection conn.Open() ' Fill the DataTable named "Advertisements" in DataSet with the rows ' returned by the query. da.Fill(dsAdvertisements, "Advertisements") Dim dsAdvertisements As DataSet = New DataSet() ' Bind the FormView control. AdvertisementForm.DataBind() End Using End Sub Server Error in '/www.mywebsite.com' Application. Incorrect syntax near ','. [Code].... Stack Trace: [Code]....
