|
dansoft wrote: can you please give me the code in vb.net format? i don't know c#.net
Are you serious? Its the same framework - the only differences are a few minor syntactical ones.
Dim cmd As SqlCommand
cmd = New SqlCommand()
cmd.Connection = myConnection
cmd.CommandText = "SELECT * FROM MyTable " & _
"WHERE SomeDate BETWEEN @startDate AND @endDate"
cmd.Parameters.Add("@startDate", theStartDateTimeObject)
cmd.Parameters.Add("@endDate", theEndDateTimeObject)
Dim reader As SqlDataReader
reader = cmd.ExecuteDataReader()
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
|
|
|
|
|
the value 1233.65774858
what i need 1233.65 but i am getting 1233.6500000000
vivek
|
|
|
|
|
Use Convert Function
convert(decimal(10,2), 1233.65774858)
Regards,
Javed
|
|
|
|
|
thanks mr. khan the method is really cool.
vivek
-- modified at 4:39 Monday 6th February, 2006
|
|
|
|
|
Hi
at the moment, I'm trying to delete all records in a table.
(1) What i do is create a dataAdapter that drags out all records and fill it into a dataset.
(2) go through each record in the dataset, and mark it as Delete
(3) Update the Database using Update from the Data Adapter.
This method seems rather slow and inefficient.
I was wondering is there another way of deleting all rows in a table without having to populate a dataset?
thanks
|
|
|
|
|
Try to use this:
Dim Conn As New SqlConnection<br />
Dim cmd As New SqlCommand<br />
Conn.ConnectionString = "Data Source=Server; Database = DB ; Integrated Security=SSPI"<br />
Conn.Open()<br />
cmd.Connection = Conn<br />
cmd.CommandType = CommandType.Text<br />
cmd.CommandText = "Delete From TableName"<br />
cmd.ExecuteNonQuery()
Regards
Javed
|
|
|
|
|
thanks a lot
your help is appreciated.
|
|
|
|
|
Hi
Say i have two tables in Access
Order and Order_Items where Order is the parent and Order_Item is the child and it is linked by the columns order_ID.
order_ID in the Order table is the primary key and done by autonumbering whilst order_ID in the Order_Items is a foreign key.
my problem is this. i have both tables and schemas put onto a dataset, where i create new rows, but i do not assign an order_ID for any of the rows i create because Access would autonumber them when i call an Update.
The problem is that how do i link Order_Items record with Order record in the dataset? would i have to update the database every time i create an Order record in order to get assigned an order_id before i can then create child items in the Order_Item table that link to this order_ID or is there some sort of feature in ADO.NET that allow you to link two records together without having to assign a value to the linking columns?
Thanks.
|
|
|
|
|
csharp_boy wrote: how do i link Order_Items record with Order record in the dataset?
well regarding linking the two tables ,I assume that u are already using a typed dataset and an xml schema with the relation for that schema as you have mentioned .
so in order to link the tables for creating new items or basically to ADD a row in a table that has a forign key you need to do this:
//define a new row just for adding it in the forignKey Field in the child table
//so my Typed Dataset is "DataSet_tll_tables"
//and you have to make the row from the class of Your DataSet
//and intilize it with DataSet Object that you are using
WindowsApplication1.DataSet_all_tables.OrderRow temp_row_for_adding = dataSet_all_tables1.Order.NewOrderRow();
//for loop to get values of the parent row in the parent table //just for adding a new row\\
for(int i=0;i
|
|
|
|
|
I'm trying to figure out why I keep getting a error searching my database with ado. Here is my search button
<br />
Private Sub CmdSearch_Click()<br />
Dim SQlnameSearch As String<br />
SQlnameSearch = txtSearch.Text<br />
Adodc1.RecordSource = " Select * FROM Customers WHERE (ContactLastName)= '" & SQlnameSearch & "' "<br />
Adodc1.Recordset.Requery <-- errors here <br />
Adodc1.Refresh <-- if the above line is commented out it errors here<br />
' want to fill the datagrid with the results of the search from above.<br />
DBGrid1.Visible = True<br />
End Sub<br />
Now if i build mt own connection like so
<br />
Dim cnn As New ADODB.Connection<br />
Dim rst As New ADODB.Recordset<br />
Dim fld As ADODB.Field<br />
<br />
' Open the connection<br />
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
"Data Source=" & App.Path & "\databases\PoolApp.mdb"<br />
<br />
strSearchCustomer = FrmSearch.Searchtxt.Text<br />
rst.Open _<br />
"SELECT ContactLastName FROM Customers WHERE ContactLastName = '" & strSearchCustomer & "' ", _<br />
cnn<br />
<br />
<br />
' Print the values for the fields in<br />
' the first record in the debug window<br />
For Each fld In rst.Fields<br />
<br />
MsgBox rst.Fields.Count <-- I get 1 record but unable to fill the datagrid. <br />
<br />
Next<br />
<br />
' Close the recordset<br />
rst.Close<br />
Any help would begreatly appreciated
Help is great only if you ask correctly
|
|
|
|
|
jlawren7 wrote: Help is great only if you ask correctly
You said it yourself! You say this code generates errors, but you never say what those errors are. It's kind of impossible to help you without knowing what the errors are.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
This error comes when using the adodc1.recordsource =
Runtime error '91'
object varible or With block varible not set
This line is highlighted in Yellow
Adodc1.Recordset.Requery
the other section ( built my own connection results in nothing )
Help is great only if you ask correctly
-- modified at 14:44 Sunday 5th February, 2006
|
|
|
|
|
You're calling Requery on a RecordSet object that is null, or Nothing in VB. You cal only call this method on a RecordSet object that has been returned by a previous query, if that previous query returned anything at all.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Well since i have tried to go a different route
Now I'm dtuck with filling a dtagrid witrh the table contents. Then once that is working I would like to use a sql search to get the records i want
here is what I have now
<br />
Option Explicit<br />
<br />
' Create a Recordset<br />
Dim rst As ADODB.Recordset<br />
<br />
Private Sub Command1_Click()<br />
<br />
Set rst = New ADODB.Recordset<br />
rst.CursorLocation = adUseClient<br />
<br />
' Add columns to the Recordset<br />
rst.Fields.Append "Customer ID", adInteger<br />
rst.Fields.Append "First Name", adVarChar, 40, adFldIsNullable<br />
rst.Fields.Append "Last Name", adVarChar, 40, adFldIsNullable<br />
rst.Fields.Append "Address", adVarChar, 60, adFldIsNullable<br />
rst.Fields.Append "Phone Number", adInteger<br />
' Open the Recordset<br />
rst.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Testing\v003\databases\db1.mdb; Persist Security Info=False"<br />
<br />
<br />
' Populate the Data in the DataGrid<br />
Set DataGrid1.DataSource = rst<br />
End Sub <br />
<br />
A simple datagrid, command button on a form <br />
|
|
|
|
|
Man, you are really confused about these object works.
You didn't perform a query against that database at all. All you did was create a blank recordset, add some columns to it, then bound the empty recordset to a datagrid.
You need to actually execute a query against the database. Something like this will return all the records in the specified table:
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Program Files\Testing\v003\databases\db1.mdb;Persist Security Info=False"
rs.CursorLocation = adUseClient
rs.Open "tableName", conn, adOpenStatic, adLockReadOnly, adCmdTable
Set DataGrid1.DataSource = rs
This probably won't run as listed. I haven't used VB6 in over 5 years now...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
connection is dao, os is winxp, I want to disable SSPI. do you know how it is possible?
|
|
|
|
|
this query show error
SELECT distinct jr.txtjcode FROM jobmast J,JobsRound JR WHERE j.txtjcode=jr.txtjcode and and convert(smalldatetime,j.dtcompletion)>= CONVERT(nvarchar(11),getdate()) ORDER BY jr.txtjcode asc, j.datecompletion asc
erro is
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
i want to show jr.txtjcode in asc order of datecompletion is a date
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
SELECT distinct jr.txtjcode,j.datecompletion FROM jobmast J,JobsRound JR WHERE j.txtjcode=jr.txtjcode and convert(smalldatetime,j.dtcompletion)>= CONVERT(nvarchar(11),getdate()) ORDER BY jr.txtjcode asc, j.datecompletion asc
include the j.datecompletion field in the select list.
|
|
|
|
|
In Sql server management studio, after creating the stored procedure, it saves it in the default folder 'Projects'. Is that where the stored procs would be saved? If I wrote it in VS, where would I save it?
|
|
|
|
|
SQL Server itself stores the stored procedures in the database. While it is always nice to have your own text file version for easy editing there isn't anyway that I've found that is a great solution. All methods I've used have all been adequate and I'm still trying to find the great method that would actually save me time and make it easy to manage.
Both methods that I've found that work adequately are both deployment script driven - in other words the stored procedures are written in such a way that deployment is easy. However, the draw back is that code maintenance is slightly more difficult.
The first solution is to bundle all the stored procedures (or bundled by subsystem [however you want to define that]) into one script. When it comes time to deploy the application to the live/production environment all you have to do is run that script to upload the stored procedures. (I tend to have separate scripts for data model changes, additions, lookup/meta data, user defined functions, stored procedures and permissions). This solution suffers from the fact that the stored procedure script becomes bloated on a large system. However, it makes it easy to deploy manually by a DBA.
The second solution puts each of the scripts in various folders. The folders are numbered so that the can be followed in sequence. One of these folders will be for stored procedures. You can put all the stored procedures into the folder and name each file as per the stored procedure. This can produce potentially hundreds (or thousands) of files so a DBA is not going to go through each folder running each script manually. I've also found that concatenating the files for deployment can sometime produce strange effects, so the solution that I found was to create an install class to add to the setup of the application that follows the folders' numbering in sequence to deploy everything to the server. However this is also difficult to maintain.
I know this does not really answer your questions - but then again, VS still doesn't have a good solution to database deployment still (unless someone wishes to prove me wrong). I also realise you didn't ask about deployment, but it is an important thing to think about when you start as it will make everything easier in the long run.
Bottom line, your SQL Scripts should be stored with your Visual Studio Solution, unless the solution covers many different technologies in which case you may have to figure out a compromise solution.
NOTE: solution (with small "s") and Solution (with a capital "S") mean different things. The latter specifically refers to a Visual Studio Solution
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
|
|
|
|
|
Hi,
I am trying to insert a datetime datatype into the Sql Server 2005 from the code in my file. The query is:
"Insert into xx (date, day) Values (" + currentdate + "," + currentday+")"
I get an error of not being able to convert the datetime to System.timespan. I dont know what it means.
can anyone please tell me what I am diong wrong
|
|
|
|
|
You haven't posted enough code to make a real diagnosis. However, from what you have said there is a DateTime object that somewhere is being converted to a TimeSpan object and it isn't able to do that - sorry, that is just paraphrasing the error message, but without the code I can't help much.
Also, the little code that you do show has security holes in it. You may like to read about SQL Injection Attacks and tips on how to solve them[^]
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 All,
I have in my database two columne number colume and varchar and i need to add this columne in one columne i try cast but it gives me invaled express.
please help
hoho
|
|
|
|
|
It is very difficult to read your request because it is just one very long sentence.
You have two columns in your database.
A number column, and a varchar column.
You need to add [some column] in one single column.
You try to cast [some column] but it gives you an error message saying invalid expression.
I hope that is correct. I've put in square brackets where there was not sufficient context in order to parse your request.
Please supply the missing context from your request. Please also supply the code that you currently have.
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
|
|
|
|
|
Hi everybody,
I am kind of new in this area... So, I need to restore some .dbf files on SQL. Could you tell me which is the best way to do that?
P.S. I don't even know the .dbf file's structure. 
Thanks a lot,
Cristina
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
