|
This will search for anything containing "name"
SELECT * FROM MyTable WHERE column LIKE '%name%'
The % is a wildcard meaning zero or more characters.
However, overuse of wildcards screws indexing and reduces the performance of the SQL Server.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanks thats where i was looking for.
Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label
|
|
|
|
|
gharry wrote: Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label
That depends on how you are extracting the data from the database.
If you are using a Data Reader then you just have to keep reading until you have all the rows. If you use a Data Adapter then fill the DataSet and find the Rows.Count from the DataTable.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
I use this code to read a CSV file into a DataGrid:
Dim objConnString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("Sql\") & ";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""
Dim objString As String = "SELECT * FROM C_Registered_horses.csv"
Dim objConn As New OleDbConnection(objConnString)
Dim objConnCommand As New OleDbCommand(objString, objConn)
Dim objConnReader As OleDbDataReader
objConn.Open()
objConnReader = objConnCommand.ExecuteReader()
dataGridConn.DataSource = objConnReader
dataGridConn.DataBind()
But it only shows the first column from the CSV file.
This methode works fine when connecting to a SQL database but why not here
|
|
|
|
|
I am trying to write a program which parses a text file, stores certain values, then inserts these values into a table. The table has one primary auto key, with 8 columns. The data is getting put into the table ( somewhat ). Several columns that hold (char) values are only insert the first character into the column. I ran the program through the debugger, and the parameter items of the data adapter hold the correct values. Here is the method that handles the parse and insert command:
private void btnStart_Click(object sender, System.EventArgs e)
{
string strFilename;
int intStoreNumber;
StreamReader objRead;
string strRecord;
int intStore;
string strNU;
string strManf;
string strModel;
string strSerial;
decimal decCost;
decimal decNet;
DateTime dtDate;
int intCount = 0;
for (intStoreNumber = 1; intStoreNumber <= 20; intStoreNumber++)
{
if (intStoreNumber < 10)
{
strFilename = intStoreNumber.ToString();
strFilename = strFilename.Insert(0,"0");
}
else
{
strFilename = intStoreNumber.ToString();
}
strFilename = "invd" + strFilename + ".prn";
objRead = new StreamReader(strFilename);
while (objRead.Peek() > -1)
{
strRecord = objRead.ReadLine();
intCount++;
if (strRecord != "" && strRecord != "\\f" )
{
if (strRecord.Substring(14,1) == "N" | strRecord.Substring(14,1) == "U")
{
if(strRecord.Substring(14,3) != "NU-")
{
intStore = intStoreNumber;
strNU = strRecord.Substring(14,1);
strManf = strRecord.Substring(26,8);
strModel = strRecord.Substring(36,10);
strSerial = strRecord.Substring(47,12);
decCost = Decimal.Parse(strRecord.Substring(60,8));
decNet = Decimal.Parse(strRecord.Substring(70,8));
dtDate = DateTime.Parse(strRecord.Substring(79,10));
SqlCommand cmdNew = new SqlCommand("ProcessInventory", this.sqlConnInv);
SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
DataSet dsInventory = new DataSet();
cmdNew.Parameters.Add("@Store_ID", SqlDbType.Int).Value = intStore;
cmdNew.Parameters.Add("@NU", SqlDbType.Char).Value = strNU;
cmdNew.Parameters.Add("@MANUF", SqlDbType.Char).Value = strManf;
cmdNew.Parameters.Add("@MODELNO", SqlDbType.Char).Value = strModel;
cmdNew.Parameters.Add("@SERIALNO", SqlDbType.Char).Value = strSerial;
cmdNew.Parameters.Add("@ORIGCOST", SqlDbType.SmallMoney).Value = decCost;
cmdNew.Parameters.Add("@NETVAL", SqlDbType.SmallMoney).Value = decNet;
cmdNew.Parameters.Add("@DATEAQ", SqlDbType.SmallDateTime).Value = dtDate;
sdaNew.Fill(dsInventory1,"Inventory");
}
}
}
}
}
}
and here is the stored procedure:
ALTER PROCEDURE dbo.ProcessInventory
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@Store_ID int,
@NU char,
@MANUF char,
@MODELNO char,
@SERIALNO char,
@ORIGCOST smallmoney,
@NETVAL smallmoney,
@DATEAQ smalldatetime
AS
/* SET NOCOUNT ON */
INSERT Inventory(Store_ID, NewUsed,Manf,Model,Serial,Cost,Net,DateAcc)
VALUES (@Store_ID, @NU, @MANUF, @MODELNO, @SERIALNO, @ORIGCOST, @NETVAL, @DATEAQ)
RETURN
The Manf,Model, and Serial columns are the ones not being updated correctly.
Here is the definition for the table:
Inv_Reco(P-Key) int 4
Store_ID int 4
NewUsed char 1 (allow nulls)
Manf char 20 (allow nulls)
Model char 20 (allow nulls)
Serial char 20 (allow nulls)
Cost smallmoney 4 (allow nulls)
Net smallmoney 4 (allow nulls)
DateAcc smaldatetime 4 (allow nulls)
I wrote another board and was told to use the Command.ExecuteNonQuery, but that did not work.
I am new to SQL databases w/ C#, so any input would be greatly appreciated
|
|
|
|
|
I notice your code does not specify in the parameter the length of the char - I'm guessing that it might be defaulting to char(1).
TheJudeDude wrote: I wrote another board and was told to use the Command.ExecuteNonQuery
They were right. Since your command is only calling a stored procedure that performs an INSERT the use of the DataAdapter is overkill. You are using an expensive object, and an expensive method (Fill) for nothing as there will be no data returned to your application with which to "fill" anything.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
OK - sorry, the char variables are actually varchar(20) except for the first (NU).
I killed that data adapter, and tried the Command.ExecuteNonQuery and I get the following error :
Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed.
But isn't the connection open fron the form?? I tried sqlConnInv.Open(), but got an error that the connection was already open...
Thanx in advance!
|
|
|
|
|
TheJudeDude wrote: But isn't the connection open fron the form??
I don't know - Did you open it before calling this method? As a rule it is better not to keep a connection hanging around, it is better to create a new connection, open it, perform the data operations then close it again and let .NET's connection pooling handle how long the physical connection hangs around for. I recommend creating a method whose purpose is to create the connection, then call that method to get your connection.
In fact, and I know this is getting further from your original problem, I would recommend that you refactor all your data access in to its own class (at least) or assembly. This keeps all the data access code together and you can benefit from being able to easily reuse common methods (like opening a connection).
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Well, I got it done yesterday. I ended up making the connection programmaticly, and modifying the stored procedure and it worked. One little quirk is on the form. I have two blank labels and a progress bar. The progress bar works fine. I pass two variables to the labels inside a for loop and while loop. The problem is that the text is not showing in the labels....odd. Any insight?
|
|
|
|
|
TheJudeDude wrote: I ended up making the connection programmaticly
 How else would you make the connection?
TheJudeDude wrote: The progress bar works fine. I pass two variables to the labels inside a for loop and while loop. The problem is that the text is not showing in the labels....odd. Any insight?
Probably because of its nature the progress bar updates the display immediately when its value is changed without waiting for paint event to fire, while the label control won't update the display until it gets a paint event, while a long task is in progress the message pump isn't firing events. You can force the label, or any control, to Refresh()[^] or you can call Application.DoEvents()[^] in order to process any other events (like button clicks, moving the scroll bar, etc.)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hello,
I'm a newbie at SQL, I can't figure out how to make the following query:
I have table 1 (2 columns)
code|otherstuff
And table 2 (2 columns)
code|some other stuff.
I'm getting the DISTINCT code, and in the same query I want to get the count(*) of records in table1 for each code, and count(*) in table2 where the code equals the code in table1. Tried already all kinds of joins but to no avail.
Any suggestions?
|
|
|
|
|
Deian wrote: I'm getting the DISTINCT code
This will be
SELECT DISTINCT code FROM Table1
Deian wrote: and in the same query I want to get the count(*) of records in table1 for each code
We can drop the DISTINCT because we'll be adding a GROUP BY for the aggregation
SELECT code, COUNT(*) as count FROM Table1 GROUP BY code
Deian wrote: count(*) in table2 where the code equals the code in table1
Now, create a similar query for Table2 and make both subqueries and join on the subqueries.
SELECT t1.code, count1, count2
FROM (SELECT code, COUNT(*) as count1 -- This select is from the previous step
FROM Table1
GROUP BY code) AS t1
INNER JOIN (SELECT code, COUNT(*) as count2 -- Similar but for Table2
FROM Table1
GROUP BY code) AS t2 ON t1.code = t2.code -- Join the two subqueries together
Does this help?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thank YOU!
I guess you are my saviour 
|
|
|
|
|
Hello again!
The query works perfect in newer servers which support subqueries, but it don't work in my case (MySQL 3.23.58). Subqueries were introduced since MySQL 4.1.
How can I rewrite your query without using subqueries in order to get the count of records in both tables?
Thank you in advance!
-- modified at 11:22 Friday 6th January, 2006
|
|
|
|
|
Hi i am havin a bit of trouble with the joins in this query as some of the values stored in the main table may be entered into the table as nulls so
when i try to join the another table using these values it will not work an i dont get back any rows does anyone have any ideas how to solve this problem
Thanks in advance Tim
select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as Name, i.Diagnosis as Injury,
SUBSTRING(CAST(i.Date as nvarchar), 0 ,12) as Date, im2.Name as Prognosis, imgoi.Name as GradOfInjury, impob.Name as PartOfBody,
imtt.Name as TissueType, imact.Name as Activity, imsur.Name as Surface, imsurcon.Name as SurfaceCondidtions, imfw.Name as Footwear,
'Recovered' = CASE WHEN i.Date IS NOT NULL and (i.DateRecovered IS NULL)
THEN 'No' ELSE 'Yes' END from injury i
join Person p on p.Id = i.PersonID
join InjuryMapping im2 on im2.ID = i.Prognosis
join InjuryMapping imgoi on imgoi.ID = i.GradeOfInjury
join InjuryMapping impob on impob.ID = i.PartOfBody
join InjuryMapping imtt on imtt.ID = i.TissueType
join InjuryMapping imact on imact.ID = i.Activity
join InjuryMapping imsur on imsur.ID = i.Surface
join InjuryMapping imsurcon on imsurcon.ID = i.SurfaceConditions
join InjuryMapping imfw on imfw.ID = i.Footwear
where (p.ID = 3 and i.ID = 87) or (p.ID = 7 and i.ID = 82)
order by p.Lastname
|
|
|
|
|
Use an OUTER JOIN, a LEFT OUTER JOIN or a RIGHT OUTER JOIN - Also, it is often better to write INNER JOIN rather than just JOIN as it makes the intent clearer.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
I have MSDE 2000 on my system. I have a boxed version of VS 2005 Pro, which comes with SQL server 2005 Developer.
I installed VS Pro but chose to exclude SQL Server 2005 Express.
Then I tried installing SQL Developer. It complains that SQL Database Services (which I assume is the basic SQL Server Engine) cannot be installed, as there's no upgrade from MSDE 2000 to SQL Developer.
What's the recommended way of doing this? Get SQL Express to upgrade the MSDE and then install SQL Developer? Or just uninstall MSDE 2000 first? I'd prefer the former if possible.
Reason I opted not to install Express is because various people have reported issues with subsequently going to one of the the full SQL Servers.
On my laptop I did go for the default VS install, followed by SQL Server Developer. This went fine, but there was no previous MSDE database on my laptop. It did, however, miss out Management Studio initially, so I had to run the install again.
Kevin
|
|
|
|
|
Dear all,
I have a problem with connection pooling. I programmed in VB.NET with ADO.NET and SQL Server. Here is my code in one form:
Dim conn as new SqlConnection(strConnectionString)
conn.Open
....
conn.Close
There are 3 SqlConnection instance in my code.
But If I run the form several times (about 25 times), it occurred an error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
I am sure that I have closed conn as soon as posible, so I do not know why that error happend.
Any comment are welcome.
Thanks for reading.
|
|
|
|
|
It will only be caused if you have some connection left open
Akif
|
|
|
|
|
Thanks for answering. At last, I found the source of problem, beacause I turn on SQL Debuging mode in Visual Studio IDE. If I turn off that mode, the problem did not apprear again.
Thanks
|
|
|
|
|
how to import a data (.xls) in sqlserver using vb codes. tnx in advance.
september
-- modified at 19:35 Wednesday 4th January, 2006
|
|
|
|
|
Hi
u try this
Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim sqlcon As New ADODB.Connection
Dim rs As New ADODB.Recordset
' Excel sheet
Path = "C:\ramani\book1.xls"
ssql = "Provider=Microsoft.jet.oledb.4.0;Data Source=" & Path & ";Extended Properties=Excel 8.0"
If con.State = 1 Then con.Close
con.Open ssql
ssql = "select * from [sheet1$]"
If rs.State = 1 Then rs.Close
rs.Open ssql, con
'Sql server
ssql = "Provider=sqloledb;Data Source=IBMX225;Initial Catalog=Master;User Id=sa;Password=;"
If sqlcon.State = 1 Then sqlcon.Close
sqlcon.Open ssql
While Not rs.EOF
sstr = "insert into excel values(" & rs(0) & ",'" & rs(1) & "')"
sqlcon.Execute sstr
rs.MoveNext
Wend
End sub
-ramani
|
|
|
|
|
I use this code to setup a connection to a CSV file:
ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("Sql\C_Registered_horses.csv") & ";Extended Prperties=""text;HDR=Yes;FMT=Delimited"""
Dim QuerySQL As String
QuerySQL = "SELECT * FROM C_Registered_horses.csv"
Dim objConn As New OleDbConnection(ConnectionString)
Dim objCmd As OleDbCommand
objCmd = New OleDbCommand
objCmd.CommandText = QuerySQL
objCmd.Connection = objConn
But when i execute the objConn.Open() i get the error:
System.Data.OleDb.OleDbException: Could not find installable ISAM
Any idea's
|
|
|
|
|
Im guessing the problem is with the connection string.
snipt from ADO Connection Strings[^]
strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
"Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;");
If you are using tab delimited files, you must create the schema.ini file, and you must inform the Format=TabDelimited option in your connection string.
for more help with connection strings see ConnectionStrings.com/[^]
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
-- modified at 1:34 Thursday 5th January, 2006
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
