|
How to approach inserting data to sql by batch number?
i have textbox and button.
if user type in textbox, let say.
'First Batch<br />
4500110 ;insert to table tblBulk<br />
4500111 ;<br />
4500112 ;<br />
4500113 ;<br />
<br />
'Second Batch<br />
4611001<br />
4611002<br />
4611003
i dont know what should i put in column2 so that
it will know the batch
---------
tblBulk
---------
column1 column2<br />
4500110<br />
4500111<br />
4500112<br />
4500113<br />
4611001<br />
4611002<br />
4611003
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Why don't you store the BATCH # with each transaction ?
|
|
|
|
|
solved. i used max query for batch no.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
I have the following code:
Imports System.Data.SqlClient
Public Class Main
Protected WithEvents DataGridView1 As DataGridView
Dim instForm2 As New Exceptions
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
"dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]" & _
"from dbo.payroll" & _
" where payrollran = 'no'"
Dim oCmd As System.Data.SqlClient.SqlCommand
Dim oDr As System.Data.SqlClient.SqlDataReader
oCmd = New System.Data.SqlClient.SqlCommand
Try
With oCmd
.Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
.Connection.Open()
.CommandType = CommandType.Text
.CommandText = ssql
oDr = .ExecuteReader()
End With
If oDr.Read Then
payperiodstartdate = oDr.GetDateTime(1)
payperiodenddate = payperiodstartdate.AddSeconds(604799)
Dim ButtonDialogResult As DialogResult
ButtonDialogResult = MessageBox.Show(" The Next Payroll Start Date is: " & payperiodstartdate.ToString() & System.Environment.NewLine & " Through End Date: " & payperiodenddate.ToString())
If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
exceptionsButton.Enabled = True
startpayrollButton.Enabled = False
End If
End If
oDr.Close()
oCmd.Connection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
oCmd.Connection.Close()
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
Dim connection As System.Data.SqlClient.SqlConnection
Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
Dim ds As New DataSet
Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime) as duration INTO scratchpad3" & _
" FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _
" where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _
" GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _
" [Exceptions].code, [Exceptions].exceptiondate"
connection = New SqlConnection(connectionString)
connection.Open()
Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
_CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
_CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
adapter.SelectCommand = _CMD
Try
adapter.Fill(ds)
If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then
MessageBox.Show("There was no data for this time period. Press Ok to continue", "No Data")
connection.Close()
Exceptions.saveButton.Enabled = False
Exceptions.Hide()
Else
connection.Close()
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
connection.Close()
End Try
Exceptions.Show()
End Sub
Private Sub payrollButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollButton.Click
Payrollfinal.Show()
End Sub
End Class
and when I run my program and press this button
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
I have my date range within a time that I know that my dataset should produce a result, but when I put a line break in my code here
adapter.Fill(ds)
and look at it in debug, I show a table value of 0. If I run the same query that I have to produce these results in sql analyser, I see 1 result. I have also verified that my data that is being passed to my query via the variables are correct.
Can someone see why my query on my form produces a different result than the sql analyser does?
Also here is my schema for my two tables:
Exceptions
employeenumber varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
exceptiondate datetime no 8 yes (n/a) (n/a) NULL
starttime datetime no 8 yes (n/a) (n/a) NULL e
ndtime datetime no 8 yes (n/a) (n/a) NULL
duration varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
code varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approvedby varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approved varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
time timestamp no 8 yes (n/a) (n/a) NULL
employees
employeenumber varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
name varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
initials varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
loginname1 varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
Thank you
Doug
|
|
|
|
|
Look here:
[DataSet]
Looks like you need to provide a Dataset name in the constructor.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy,
I have this name as my dataset
Dim ds As New DataSet
I looked over the link that you sent me .... and I think that I'd get an error if my data adapter wasn't built correctly.
|
|
|
|
|
After the sql statement is set (including the parameters), take the value from the IDE in a debug session and apply it directly in the database (if MS Access, create a new query) and run it to make sure that you _actually_ get the results you expect.
HTH
|
|
|
|
|
HTH,
here is the exact query (with variables included) that I just tried in my sql query analyzer:
SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime) as duration INTO scratchpad3
FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber
where [Exceptions].exceptiondate between '5/2/2010 00:00:00' and '5/8/2010 11:59:59'
GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime,
[Exceptions].code, [Exceptions].exceptiondate
and that produces results.
|
|
|
|
|
Doug Ancil wrote: HTH,
"HTH" is not my name... it is short for "hope that helps"
Doug Ancil wrote: here is the exact query (with variables included) that I just tried in my sql query analyzer:
Just looked at your query again - doesn't a "select into" query create a table? it does not really return a result? How about creating a statement with a select * from the [new table]?
|
|
|
|
|
Chandra,
Sorry about that, but what I'm trying to do is to display that data back into a datagrid view (which is why the adapter.Fill(ds)) is there. Are you suggesting that after I run the query, to then create a select* from that table and then populate that to a datagridview via an adapter?
|
|
|
|
|
Doug Ancil wrote: Are you suggesting that after I run the query, to then create a select* from that table and then populate that to a datagridview via an adapter?
Yes.
|
|
|
|
|
Chandra,
Can you give me a good example of that that wouldnt require me to re-write a lot of code?
|
|
|
|
|
How about using a dataReader.ExecuteNonQuery on the first sql and then a data adapter for the select * to fill the data set?
|
|
|
|
|
from the sample:
Dim dataSet As DataSet = New DataSet("Suppliers")
...
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Doug,
For your future reference, could you use <pre lang="vb"> your code goes here </pre> instead of <code></code>. The pre tags preserve formatting and give some syntactical colouring. As you will have seen, the code tags just give a horrible reddish colour. this hurts my eyes so much (genuinely) that I am unable to read your code, not that I could have helped anyway .
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
I wouldn't let CG touch my Abacus!
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is.
|
|
|
|
|
Henry,
Thanks for that. I've now fixed the code.
|
|
|
|
|
FYI[^]
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Hi,
What is the best way to store/retrieve binary data in/from SQL server DB via VB.Net.
I have inheritate an SQL server database that has stored PDF files in a table and not got a clue in how to store or retrive the binary data. I plan to write a VB.Net application to display the contents.
Any ideas?
|
|
|
|
|
|
Is it possible to make the tab (a text 'Main Report' appears in it) invisible that appears in a CrystalReportViewer just below the Toolbar? I know it shows which section of the report is opened currently but in certain scenario I need it not to be visible. Please help. Regards.
|
|
|
|
|
priyamtheone wrote: Is it possible to make the tab (a text 'Main Report' appears in it) invisible that appears in a CrystalReportViewer just below the Toolbar? I know it shows which section of the report is opened currently but in certain scenario I need it not to be visible.
Are you referring to the group tree? If so, you can set it to not show by setting the DisplayGroupTree value to false.
HTH
|
|
|
|
|
Hello Everybody,
How can i add Embedded Menifest With Appliation of Visual Basic 2005 (Framework 2.0)
Thanks & Regards
If you can think then I Can.
|
|
|
|
|
|
Hi all, I'm going to finish the IT professional and management have to develop a final draft of the course. My project will be a program for managing a business lunch. Still have not programmed anything, just created the forms. When I change any label or whatever is the form the program does not change, crashes and it takes ages to open. If someone can explain to me why thanks.
|
|
|
|
|
exotiick wrote: why
because you did something wrong. Without any information to work on, that is as specific an answer you will get.
If there is a compile-time or run-time error, look at it; or report it here.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|