|
hi everybody
i have two tables
table1 with column name
SubmissionDate nvarchar(11)
table2 with column name
submissiondate2 (datetime)
and i insert data in table1 like 17/05/2009 but still in nvarchar format
cos this is my requirement (i mean nvarchar)
my question is i want to insert same date from table1's column to table2's column but dont know how to convert datatype nvarchar to datetime.
when i try to cast i got this error msg
Arithmetic overflow error converting expression to data type datetime.
any help would be really appricated
|
|
|
|
|
|
insert into table2
select cast(submissiondate2 as datetime) from table1
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This is a fundamental error that a lot of newbies make - storing dates as strings. Your dates are stored in a format other than yyyy-mm-dd, probably dd/mm/yyyy and SQL cannot decide between the dd and mm structures, therefore the error.
Try using Convert(datetime,datefield,106)
or one of the other formatters. Under convert/cast in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks holmes for your kind answer
my Problem;
the following code is working perfectly except date. every thing is working perfectly pleas let me know how to add Date into table through following query, and dont worry about other variables these are working perfectly because i have changed them into varchar like this
SELECT @str_Session_Id=CONVERT(nvarchar(10), CONVERT(int, @SessionId))
but i dont want to add date like varchar because i have to compare date with other dates therefore i dont want to convert into varchar.
select @date =(select DATEADD(dd, 1, GETDATE()))
Begin
set @txtSql =
case when (@Std_Count < 3) then 'insert into ProjectRequests values('+ @str_stu_Id+','+ @str_Priority_Id +','+ @str_Project_Id +' , '+ @str_Session_Id +', '+ @date +' , NULL)'
Msg 241, Level 16, State 1, Procedure FUALLY_QUALIFIED_STUDENT_BULK, Line 48
Conversion failed when converting datetime from character string.
any help would be appreciated.
|
|
|
|
|
Do some research into parameterised queries, they will make your life so much easier.
Inserting date using a string var is Ok, you just need to format it correctly, the following are valid formats for SQL Server. The important thing is to STORE it as a datetime data type in the database.
yyyy/mm/dd
dd/MMM/yyyy
mm/dd/yyyy
If you try to use a date in dd/mm/yyyy format it will create the error you are getting. MS is a US company so SQL Server used the stupid freakin US date format internally.
Also you need to add additional ' around your date value.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Having installed SQL Server on the same machine where my application
is running, under connection string I could mention Server and Database as follows
ConnectionString = "Server=ABCD-AC93CE29C0\SQLEXPRESS;Database=MyDatabase"
What if SQL server was on a different machine. How do I give the path of that
external machine on which my SQL Server is running?
|
|
|
|
|
This is a good resource for connection strings[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In my application, I retained membership provided by aspnetdb
For other requirements in the application, I created a database and tables
in my SQL Server 2005.
My problem is I get an error message "Stored Procedure 'abcd' not found.
I have added Stored Procedure using SQl Server Management Studio for that database.
Where could the sp dissappear???
I canot see the sp in SQL Server Management Studio's object browser either!!!
Where could the sp dissappear???
|
|
|
|
|
SPs dont just desappear
Probably you created the sp in another DB (maybe the aspnet membership)
You are saying you have 2 DBs, make sure you have 2 connection strings as well and are not trying to access both DB with a single conection string
Dont complicate your life, just recreate the sp in the correct DB
Alexei Rodriguez
|
|
|
|
|
Hi Alex,
Actually I Missed the Execute Command after creating a stored procedure.
Thanks,
NetQuestions
modified on Sunday, May 17, 2009 1:37 AM
|
|
|
|
|
Hi All,
I am new in MYSQL Database. I am using the interface SQLYog tool. I just want simple stored procedure with INSERT query and it should be handled exception handling(Try-catch).
If the records are inserted in a table then the procedure should return 1. If the records are not inserted(because Exception may occured) then the procedure should return 0.
Cau anybody help me?
Please..
Thanks,
Periyasamy Ramachandiran.
|
|
|
|
|
See this[^] setting handlers. To see the insertion/updation/deletion failure/success status, check row_count(). Something like,
--your insert query
IF (<a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count">row_count()</a>[<a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count" target="_blank" title="New Window">^</a>]> 0) THEN
--insertion success
ELSE
..failure
|
|
|
|
|
Navaneeth,
Thanks for your reply. Let me try this.
-Periyasamy Ramachandiran.
|
|
|
|
|
I have a database called db on pc1 with 4 tables (table1-4) which already has information in it. On PC2 I have the same datatbase db with 8 tables (4 new tables). How can i add the 4 new tables, and data stored in them, in the database db on pc1 without losing data stored on pc1?
|
|
|
|
|
You can use the sql server import and export data wizard
Alexei Rodriguez
|
|
|
|
|
Pls help me,
i designed a database with ms sql and intend to access it using a mobile device.but at the moment am using the emulator in Visual studio 2008 professional to test the app but it cant access the database, which is running on my system.Am using vb.net for the coding given below, but sqlException keeps on coming out:
Private Sub mnuLoadSqlServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoadSqlServer.Click
Dim cn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
Try
' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=CHINEDUM\SQLEXPRESS;Initial Catalog=medihealth;Integrated Security=SSPI;User ID=CHINEDUM\CHINEDU;Password= ;")
cmd = New System.Data.SqlClient.SqlCommand(txtSelect.Text, cn)
da = New System.Data.SqlClient.SqlDataAdapter(cmd)
' Is this table being added to the DataSet? If not destroy and
' recreate the DataSet.
If Not chkAddToDataSet.Checked Then
ds = Nothing
ds = New System.Data.DataSet
End If
' Add the new table.
Dim dt As DataTable = New System.Data.DataTable(txtTableName.Text)
da.Fill(dt)
ds.Tables.Add(dt)
' Display the DataSet.
DisplayData()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Sub DisplayData()
Dim dt As System.Data.DataTable
' Clear the ComboBox control.
cmbTables.Items.Clear()
' Load the ComboBox with a list of available tables.
For Each dt In ds.Tables
cmbTables.Items.Add(dt.TableName.ToString)
Next
' Finally, trigger the displaying of the first table.
cmbTables.SelectedIndex = 0
End Sub
Note: I've tried to use my ip address as the data source, but cant still get through
|
|
|
|
|
nedusmile wrote: but sqlException keeps on coming out
And whats the exception??
But anyways, it could be that you are gettin the error that sql couldnt stablish a connection to the server, it could be that under default settings...
If so, you need to configure your sql server to accept remote connections
Alexei Rodriguez
|
|
|
|
|
Thanks Alexie, but i did enabled the TCP/IP config of my sql server,
is there any other thing that i am lacking as am new to mobile application, I dont just know where to start.
more explanation, article and links will be deeply appreciated.
Thank you very much for your time.
Nedusmile
|
|
|
|
|
Whats the exception??
What version of sql are you using??
Do you really think that someone is gonna help you like that??
Besides... thins is not a mobile development forum
Alexei Rodriguez
|
|
|
|
|
nedusmile wrote: ' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=CHINEDUM\SQLEXPRESS;Initial Catalog=medihealth;Integrated Security=SSPI;User ID=CHINEDUM\CHINEDU;Password= ;")
Use the IP with the port number that the server is listening to
Example 192.168.1.65:1212
Also, dont use integrated security, use a userid and a password
And please, tell me the damn exception, i couldn sleep last night :p
Alexei Rodriguez
|
|
|
|
|
Forgive my inexperience, Alexie. the Exception error looks something like this
system.Data.sqlClient.sqlException:sqlException
at
system.Data.sqlClient.sqlConnection.OnError()
at
system.Data.sqlClient.sqlInternalConnection.OnError()
at
system.Data.sqlClient.TdsParser.ThrowExceptionAndWarning()
at
system.Data.sqlClient.TdsParser.Connect()
at
system.Data.sqlClient.sqlInternalConnection.OpenAndLogin()
I now modified the parameters as u suggested:
Private Sub mnuLoadSqlServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoadSqlServer.Click
Dim cn As System.Data.SqlClient.SqlConnection
Dim cmd As System.Data.SqlClient.SqlCommand
Dim da As System.Data.SqlClient.SqlDataAdapter
Try
' Establish a connection to the SQL Server database.
cn = New System.Data.SqlClient.SqlConnection("Data Source=192.168.1.130,1212;Initial Catalog=medihealth;User ID=CHINEDUM\CHINEDU;Password= administrator;")
cmd = New System.Data.SqlClient.SqlCommand("SELECT * from LAB where Patient_ID=100054201", cn)
da = New System.Data.SqlClient.SqlDataAdapter(cmd)
' Is this table being added to the DataSet? If not destroy and
' recreate the DataSet.
If Not chkAddToDataSet.Checked Then
ds = Nothing
ds = New System.Data.DataSet
End If
' Add the new table.
Dim dt As DataTable = New System.Data.DataTable(txtTableName.Text)
da.Fill(dt)
ds.Tables.Add(dt)
' Display the DataSet.
DisplayData()
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
End Try
End Sub
End Class
Any other thing i should try?
Thanks a lot!!!
|
|
|
|
|
nedusmile wrote: cn = New System.Data.SqlClient.SqlConnection("Data Source=192.168.1.130,1212;Initial Catalog=medihealth;User ID=CHINEDUM\CHINEDU;Password= administrator;")
Youd better be kidding with the port number
I said 1212 is a example, is your sql server really configured to listen to port 1212??
Anyways, there could be many reasons for not being able to connect to sqlserver from your device or emulator, is it even cradled?, does it have a connection to the pc?, can you see the pc form the device or emulator?
If you want to make sure your connection string is correct, make a win or web app and try to connect with that connection string
You need to learn to read and to use google or something
You cant even spell my name, its the third time you spell it wrong
Alexei Rodriguez
|
|
|
|
|
yes all the things u specified have been configured, i can c my pc on the emulator
the port num of server is configured to listen to that given in the code.
by the way, sory for misspelling ur name, Alexei.I have googled my eyes to any possible site that could be of any help. The connection string is from "The Definitive Guide to the .NET Compact Framework" written by Larry Roof and Dan Fergus.
I have also visited connectionstrings.com and tried some of the ones given for this purpose.
Am still lost, Thanx for ur help though!
|
|
|
|
|
Did you already try the connection string you are using in your mobile app from a windows or web app???
Alexei Rodriguez
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
