|
If you mean that you want to retrieve records that have a particular word in the description:
SELECT * FROM myTable WHERE myDescriptionField LIKE '%myWord%'
Steve
|
|
|
|
|
Thanks that is what I wanted to know
|
|
|
|
|
I have a datagrid that is populated in two columns with a smalldatetime value. The column is only reflecting the date portion in the datagrid, but the full datetime in the query. How can I format this comlum to reflect just the time portion of the value?
Thanx in advance
Jude
|
|
|
|
|
Create a DataGridTableStyle. Then Create DataGridColumnStyle for each column. In the format property for the column you mention, enter:
"t" - for ShortDateTime
"T" - for LongDateTime
Steve
|
|
|
|
|
Thanx for the reply. Could you give an example of this or a link explaining this?
Thank you
Jude
|
|
|
|
|
Are you using Visual Studio?
If so, you can do this using the properties window in the designer.
Or do you need code?
Steve
|
|
|
|
|
I found it..I'll work on it a little and figure it out.
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
|
|
|
|
|
Greetings:
I am creating an SQL table programmatically using C#. I have set up my SqlCommand and I am using ExecuteNonQuery to execute the following SQL statement:
<br />
CREATE TABLE StationData (StnID INT NOT NULL AUTO_INCREMENT, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);<br />
This statement is throwing an exception and the exception message says: "Incorrect syntax near "AUTO_INCREMENT"".
If I remove the AUTO_INCREMENT specifier from the above statement, then the operation completes successfully and the table and columns are created as expected. It just seems to be that AUTO_INCREMENT specifier.
I have checked many similar example statements from various sources and I don't see anything wrong with what I have done here.
Any suggestions?
Thanks,
Mark
|
|
|
|
|
Try this:
CREATE TABLE StationData (StnID INT NOT NULL <code>IDENTITY </code>, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
OK, thanks. It worked.
Just wondering, however, IDENTITY seems to have the same effect as AUTO_INCREMENT. What then, is the status of the AUTO_INCREMENT specifier? Obsolete? Why was SQL rejecting it?
Thanks,
Mark
|
|
|
|
|
MySQL syntax is AUTO_INCREMENT[^]
SQL Server is IDENTITY [^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I'm going to have to watch out for that. I've seen a lot of web tutorials and references that talk about AUTO_INCREMENT but it turns out that they were all talking about MySQL, not SQL. I had assumed that they would be similar in syntax but this is turning out to be not the case.
Thanks again,
Mark
|
|
|
|
|
I'm working with an existing database so splitting date values into seperate DD, MM & YYYY columns is not ideal. Is there a way to cast smalldatetime to British date format: DD/MM/YYYY ?
Thanks.
|
|
|
|
|
Look up CONVERT in SQL Books Online
CONVERT ( SMALLDATETIME , your_smalldatetime_field , 103 )
|
|
|
|
|
I need to write a stored procedure that will copy rows from one table and insert them an another table based on a unique id. Any Ideas? I am very new to writing stored procedures so any help would be greatly appreciated.
Thank You
|
|
|
|
|
To create stored procedure write following
CREATE PROCEDURE [procedureName]
After that, just write your query.
Example:
CREATE PROCEDURE copyData<br />
@selectID int <br />
<br />
AS <br />
<br />
SELECT ID, Name <br />
INTO newTable<br />
FROM origTable <br />
WHERE origTable.ID = @selectID
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
Try this
Copying a Table into an Existing Table
insert into originaltable select * from mytablecopy where [Condition]
Navi
|
|
|
|
|
Hi
How To Connect To Table DBF with OLEDB
VB.net
|
|
|
|
|
Imports System.Data.OleDb
...
Dim cnn As New OleDbConnection("Provider=VFPOLEDB.1;Data Source=D:\Temp\MyTable.dbf;")
cnn.Open()
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Dim con As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=dBASE IV;User ID=Admin;Password=")
con.Open()
da = New OleDbDataAdapter("select * from table1.dbf", con)
da.Fill(ds)
"Error In Da.Fill(ds)"
|
|
|
|
|
Dim con As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
con = New OleDbConnection("Provider=VFPOLEDB.1;Data Source=c:\table1.dbf;")
con.Open()
da = New OleDbDataAdapter("select * from table1.dbf", con)
da.Fill(ds)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record.
So the procedure is:
CREATE PROCEDURE AddListing
@Name varchar(150),
@Description varchar(1000),
@Keywords varchar(50),
@Address varchar(200),
@Telephone varchar(50),
@Fax varchar(50),
@Mobile varchar(50),
@Email varchar(50),
@URL varchar(50),
@Aproved bit,
@Premium bit,
@UserID int,
@nextID int Output --
|
|
|
|
|
Check out this link
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
Navi
|
|
|
|
|
It is a bad practice to get your ID using MAX(). You should define the l_id column as an IDENTITY column, and allow SQL Server to create the ID for you. Then, make your sproc like this:
CREATE PROCEDURE AddListing
@Name varchar(150),@Description varchar(1000),@Keywords varchar(50),
@Address varchar(200),@Telephone varchar(50),@Fax varchar(50),
@Mobile varchar(50),@Email varchar(50),@URL varchar(50),
@Aproved bit,@Premium bit,@UserID int,@nextID int Output --<the output
AS
SET NOCOUNT ON
INSERT INTO listings (l_name, l_desc, l_keywords, l_address, l_tel, l_fax,
l_mob, l_email, l_url, l_premium, l_approved)
VALUES @Name,@Description,@Keywords,@Address,@Telephone,@Fax,@Mobile,@Email,
@URL,@Premium,@Aproved)
<code>SET @nextID = SCOPE_IDENTITY()</code>
INSERT INTO usr_ls_line (usr_id, l_id)
VALUES (@UserID,@nextID)
return @nextID
Then, in VB.NET do something like this:
Imports System.Data
Imports System.Data.SqlClient
...
Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mytestdb;" & _
"Integrated Security=SSPI"
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "AddListing"
Dim prm1 As New SqlParameter("@Name", SqlDbType.VarChar, 150)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"
Dim prm2 As New SqlParameter("@Description", SqlDbType.VarChar, 1000)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "Whatever"
'...
<code>Dim prm5 As New SqlParameter("@nextID", SqlDbType.Int)
prm5.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm5)</code>
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
MsgBox(<code>"Returned ID=" + prm5.Value</code>)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
In this case yes, an IDENTITY column would work work, yet you can't make such a blanket statement that not using it is bad practice. Perhaps there is some special processing that must take place to assign an ID, or the ID field is not numeric.
only two letters away from being an asset
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
