Send variable contents to SQL server table

[bryandj23]

Hello All.

I have a VB application that I'm working on that reads in data from a serial port. Currently, the app is designed to take in the data, display in a text box, and also dump to a text file. I further incorporated routines to break the data up into seperate variables. All of this is working correctly.

What I would like to do is put the contents of the variables into a table on my MSSQL server (2k). I've been looking all over for simple instructions or how-to's of how to create the ADO connection (I've seen many programming examples, but haven't tried any as of yet), and to simply INSERT the variables into the table.

To make this easier, let's say I have 8 variables, var(1) through var(8).

My database table is already created (using SQL administrator), so I'm thinking the inserting should be relatively simple?

Any samples or referrences to the code I need (and maybe a bit of explanation) would be greatly appreciated.

Thanks very much in advance.

Bryan Fulkersin

 

[gmmastros]

I'll help.

Before we get started, there are a couple decisions you need to make.

1. How will you be authenticating to the SQL Server? SQL Server allows for 2 different authentication methods. Windows Authentication is usually the easiest, but you can also use SQL Server Authentication which requires a user name and password.

2. We should review the table you plan on storing this information in. This includes a review of the data types. This step is important because a bad decision here could cause performance issues later. Also, a poorly designed table will be more difficult to work with.

My recommendation would be to create a stored procedure in the database that inserts the data. Then, from the VB front end, you would create an ADODB.Connection object and An ADODB.Command object. You would then use the command object to call the stored procedure.

First, we need to create the stored procedure. To help with this, I'll need to know what the table structure looks like. Open Query Analyzer and run the following command (replacing TableName with your actual table).

Select  Column_Name, 
        Data_Type, 
        Character_Maximum_Length
From    Information_Schema.Columns 
Where   Table_Name = 'TableName'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

Hi George. Thanks for chiming into help. Just so you know my background, I'm familiar with SQL in very basic form; our firm's customer data and management system are all SQL based, so I'm pretty up to speed on the basics.

Visual Basic is slowly coming back to me. I did have a VB class in college (I think it was VB5), and I was quite proficient with that. Ok, now with that out of the way:

1. I will be using windows authentication for this particular database. We use SQL authentication for our management systems.

2. Here's the table layout:
id float NULL
Call_Type varchar 1
Date datetimeNULL
Time timestamp 8
Called_Number varchar 15
Tag varchar 1
Duration datetime NULL
Line int NULL
Station int NULL
Account varchar 15
Talk_Time varchar 5

I've read alot about stored procedures. I'll admit that this is the point where I am absolutely clueless.

Thanks again for the help!

 

[gmmastros]

Please explain the Time column (data type = timestamp). If you want to store the Date and Time for the call, then I would recomend you use 1 column to do this. The DateTime data type stores, well... Date and Time.

Also, I have a general aversion to the Float data type. Since this column is named ID, I would recommend the INT data type. The problem with Float (and Real) is that they are approximate data types.

With that being said, stored procedures are easy to work with once you get used to them.

Now, open Query Analyzer and create this stored procedure. Make sure you change the table name.

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] InsertCall
	@Id [COLOR=blue]float[/color],
	@Call_Type [COLOR=blue]varchar[/color](1),
	@date [COLOR=#FF00FF]DateTime[/color],
	@Time [COLOR=blue]TimeStamp[/color],
	@CalledNumber [COLOR=blue]VarChar[/color](15),
	@Tag [COLOR=blue]VarChar[/color](1),
	@Duration [COLOR=#FF00FF]DateTime[/color],
	@Line [COLOR=blue]Int[/color],
	@Station [COLOR=blue]Int[/color],
	@Account [COLOR=blue]VarChar[/color](15),
	@Talk_Time [COLOR=blue]VarChar[/color](5)
[COLOR=blue]AS[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Insert[/color] 
[COLOR=blue]Into[/color]   TableName(
         Id, 
         Call_Type, 
         [COLOR=blue]Date[/color], 
         [COLOR=blue]Time[/color], 
         Called_Number, 
         Tag, 
         Duration, 
         Line, 
         Station, 
         Account, 
         Talk_Time)
[COLOR=blue]Values[/color]   (@Id, 
         @Call_Type, 
         @Date, 
         @Time, 
         @Called_Number, 
         @Tag, 
         @Duration, 
         @Line, 
         @Station, 
         @Account, 
         @TalkTime)

After creating the stored procedure, test it in query analyzer by hard coding values. Open a new window and type this...

[tt][blue]
InsertCall 1, 'a', '20070605', NULL, '123456789', '1', '19000101 0:20', 1, 1, 'abc123', 'hello'
[/blue][/tt]

If this runs without any errors, then you are ready to begin the VB side.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

George,

The data I'm receiving over the serial port has the date and time in seperate "fields". I guess I could combine them, though I assumed having them seperated would be easier to generate output from the SQL database once this phase is completed and working. Since I couldn't find a datatype of "DATE" and one of "TIME", I just went with what looked good. I know, probably not the way to go.

The id was just put in there by me so that there would be a key within the table. I wasn't exactly sure how to define the datatype. I will change this to INT.

There were a few errors in creating the procedure, but I managed to figure those out (some underscores were needed in your code). This has now completed succesfully.

Running the store procedure gave me errors with datatypes, so I changed them to the following:

date varchar(8)
time varchar(5)
duration varchar(8)
Talk_Time varchar(8)

After updated both the table and stored procedure with this data, and running the procedure, it sucessfully created my first record.

 

[gmmastros]

Can you show me some sample data from those fields. Specifically the date, time, duration, and talk_time data. To be specific, I want to see typical values that you want to store in the database.

I've been 'doing this' for a lot of year. Please trust me when I say you don't want to store date and time in to seperate fields.

Also, since you say that the ID column is going to be a manufactured identity column, I suggest you make it a proper identity column. The easiest way to do this is to open Enterprise Manager and change it there. When you insert in to a table with an identity column, you dont insert that field. Leaving it out of the insert statement will cause the ID column to assign the next id value to it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

Hi George.

The 'id' field under enterprise manager is showing it to be the Primary Key. Is that what you were referring to?

Here's the data that will be placed in the table:

Call_Type = C (It will always be "C" for our setup)
Date = 05/30/07
Time = 11:51 (24 hour clock)
Called_Number = 1234567890 (any combo of 15 char. max)
TAG = * or ! or ?
Duration = 00:02:26
Talk_Time = 04:04

Does this help?

 

[gmmastros]

Yeah. This helps a lot.

My suggestion is to have a column called CallDateTime, another column called Duration, and another named Talk_Time. All 3 of these columns should have the DateTime data type. If you store the data as varchar, then you will have problems running reports.

In SQL Server, a DateTime data type will always have a date component AND a time component. If the time component 'appears' to be blank, it will actually be set to midnight. If a date component appears to be blank, the date will actually be set to Jan 1, 1900. You can test this in Query Analyzer like this...

Declare @Duration DateTime
Set @Duration = '00:02:26'

Select @Duration

The output will be: 1900-01-01 00:02:26.000

By storing this as a DateTime data type, you will be able to sum the duration column (grouped by day) to get the total duration for the day. Likewise, you'll be able to do the same for Talk_Time.

It doesn't matter that you have the data in seperate fields in your vb app. You can combine date and time within the stored procedure.

As for the ID field...
1. Open Enterprise Manager
2. Drill down to table
3. Right click on table, click Design
4. Change the data type to INT.
5. At the bottom of the window, set Identity = Yes
6. Make sure Identity Seed = 1
7. Make sure that Identity Increment = 1
8. Then, remove the time column.

Then, you will need to change the stored procedure to...

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] InsertCall
    @Call_Type [COLOR=blue]varchar[/color](1),
    @date [COLOR=#FF00FF]DateTime[/color],
    @Time [COLOR=#FF00FF]DateTime[/color],
    @CalledNumber [COLOR=blue]VarChar[/color](15),
    @Tag [COLOR=blue]VarChar[/color](1),
    @Duration [COLOR=#FF00FF]DateTime[/color],
    @Line [COLOR=blue]Int[/color],
    @Station [COLOR=blue]Int[/color],
    @Account [COLOR=blue]VarChar[/color](15),
    @Talk_Time [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Insert[/color] 
[COLOR=blue]Into[/color]   TableName(
         Call_Type, 
         Date,
         Called_Number, 
         Tag, 
         Duration, 
         Line, 
         Station, 
         Account, 
         Talk_Time)
[COLOR=blue]Values[/color]   (@Call_Type, 
         @Date + @Time, 
         @Called_Number, 
         @Tag, 
         @Duration, 
         @Line, 
         @Station, 
         @Account, 
         @TalkTime)

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

Notice that the stored procedure has 2 parameters for Date and Time (@Date and @Time). Also notice that these 2 columns are added together when inserting in to the table. This is fine and will work properly as long as the @date parameter does NOT have a time component and the @Time parameter does not have a date component.

Also notice that we no longer pass ID to the stored procedure. Also notice that we don't insert the ID value in to the table. This is how it is supposed to be done. Finally, notice that we select Scope_Identity() at the end. This will return the identity value that was just inserted in to the database.

I know this is a lot of information. But once you understand this, your database skills will be a lot better. The rest (VB Code) will be fairly easy to put together.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

Ok. Now I think I'm getting it.

I've changed everything to what you have described above. So now what's the next step (VB)?

 

[gmmastros]

Yes. VB. For demonstration purposes, create a new VB project.

Then, you'll need to have a reference to ADO. To do this...

In VB
1. Click Project
2. Click References
3. Select 'Microsoft ActiveX Data Object 2.x Library'
4. Click OK

Put a button on the form (it's name will be Command1).

In the code section, copy/paste this...

Option Explicit

Private Sub Command1_Click()
    
    Dim ID As Long
    
    ID = InsertCall("A", Date, Time, "123456", "!", "00:02:20", 1, 2, "Abc 123", "00:04:30")
    
    MsgBox "New Id = " & CStr(ID)
    
End Sub

Public Function InsertCall(ByVal Call_Type As String, ByVal Call_Date As String, ByVal CallTime As String, ByVal CalledNumber As String, ByVal Tag As String, ByVal Duration As String, ByVal Line As Long, ByVal Station As Long, ByVal Account As String, ByVal TalkTime As String) As Long
    
    Dim DB As ADODB.Connection
    Dim oCommand As ADODB.Command
    Dim RS As ADODB.Recordset
    
    Set DB = CreateObject("ADODB.Connection")
    DB.CursorLocation = adUseClient
    DB.ConnectionString = "Provider=sqloledb;Data Source=(local);Integrated Security=SSPI;Initial Catalog=TekTips"
    Call DB.Open
    
    Set oCommand = CreateObject("ADODB.Command")
    Set oCommand.ActiveConnection = DB
    oCommand.CommandType = adCmdStoredProc
    oCommand.CommandText = "InsertCall"
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Call_Type", adVarChar, adParamInput, 1, Call_Type))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("date", adDate, adParamInput, , Call_Date))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Time", adDate, adParamInput, , CallTime))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Called_Number", adVarChar, adParamInput, 15, CalledNumber))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Tag", adVarChar, adParamInput, 1, Tag))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Duration", adDate, adParamInput, , Duration))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Line", adInteger, adParamInput, , Line))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Station", adInteger, adParamInput, , Station))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Account", adVarChar, adParamInput, 15, Account))
    Call oCommand.Parameters.Append(oCommand.CreateParameter("Talk_Time", adDate, adParamInput, , TalkTime))
    
    Set RS = oCommand.Execute
    InsertCall = RS("ID")
    RS.Close
    Set RS = Nothing
    Set oCommand = Nothing
    DB.Close
    Set DB = Nothing
    
End Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh. Uh... Where I show the connection string, you'll need to change that so that your server and your database are used.

DB.ConnectionString = "Provider=sqloledb;Data Source=[!]YourServerName[/!];Integrated Security=SSPI;Initial Catalog=[!]YourDatabaseName[/!]"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

By George, I think I've got it (Sorry, I couldn't help myself).

Thank you so very much for the help. After reading through the coding logic, I think I'm able to implement this into my serial dumping program. I still need to look at this a bit more in depth to understand exactly what is happening, but I think I've got the general idea pretty down pat.

Thanks again George. Have a good one!

 

[gmmastros]

I'm glad I was able to help.

Generally, the process we followed here is how I implement database functionality in to my own app.

1. determine the storage requirements.
2. create the table, choosing the most appropriate data types.
3. write the stored procedure.
4. test the stored procedure in query analyzer.
5. write the VB code to interact with the database.

There's nothing worse than thinking your database code works perfectly, then write the vb code only to have it fail. The problem is that you could spend a long time blaming the VB code only to find out it's the database code that's not working properly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

I have the program sucessfully dumping the serial data to SQL. The only issue I'm having is the DATE that is getting added to the table.

The date data coming off the serial port is 06/06/07. The date showing up in SQL is 6/4/2007. The time is correct, however.

On my form, for debugging purposes, I'm displaying each variable as it's being stripped from the entire line of data coming from the serial port. The date field shows up correctly on my form.

Any quick suggestions on where to look?

 

[gmmastros]

Hmmmm.....

You can think of dates as a number. The whole number part refers to the number of days that have elapsed since a 'start date' and the fractional number represents the time. So 0.5 would represent noon. 0.75 represents 6:00 PM.

I know that in VB, the 'Start Date' is Dec 30, 1899. In SQL Server, the 'Start Date' is Jan 1, 1900 (Exactly 2 days later).

If everything is kept in date data types, there should be no problems. But if you are converting the dates to integers and then back again, you could have a problem.

Example...

In vb, if you run this...

MsgBox cLng(Date)

You will get 39239 because that is how many days have elapsed since Dec 30, 1899.

If you convert this number to a date in SQL Server, you get...

2007-06-08 00:00:00.000

In short... I suggest you look for integer conversions, possibly with the parameters to your stored procedure and/or the command object parameter.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

Hmmm...

Within VB, the 'CallDate' variable is a string. As far as I can tell, the conversion to datetime format occurs within the Call oCommand.Parameters (stored procedure).

Would there be a downside to having the date and time stored in the table as a string rather than datetime?

 

[gmmastros]

Downside to storing dates as string? Definetely. You would have a lot harder time running some interesting reports.

I now know what the problem is.

Like I said earlier, the 'start dates' are different between vb and SQL server. So, when you have a time of '11:30' in VB, it's actually 'Dec 31, 1899 11:30 AM'. in the stored procedure, we add the date and the time. Earlier (several posts up), I said that adding a date and a time would work properly as long as there wasn't a date component. Well... there is always a date component, but I was 'hoping' it would be the 0 date. But since they are different, you are having a problem. Really, though, it's not hard to accomodate this, and can be done from VB or in the stored procedure. Personally, I prefer to do this kind of work in the stored procedure because you never know when the stored procedure will be called from another language (think c, vb.net, asp, php, cold fusion, etc...)

Anyway... Here's how to fix your problem.

[COLOR=blue]Alter[/color] [COLOR=blue]Procedure[/color] InsertCall
    @Call_Type [COLOR=blue]varchar[/color](1),
    @date [COLOR=#FF00FF]DateTime[/color],
    @Time [COLOR=#FF00FF]DateTime[/color],
    @Called_Number [COLOR=blue]VarChar[/color](15),
    @Tag [COLOR=blue]VarChar[/color](1),
    @Duration [COLOR=#FF00FF]DateTime[/color],
    @Line [COLOR=blue]Int[/color],
    @Station [COLOR=blue]Int[/color],
    @Account [COLOR=blue]VarChar[/color](15),
    @Talk_Time [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Insert[/color] 
[COLOR=blue]Into[/color]   TableName(
         Call_Type, 
         [COLOR=blue]Date[/color],
         Called_Number, 
         Tag, 
         Duration, 
         Line, 
         Station, 
         Account, 
         Talk_Time)
[COLOR=blue]Values[/color]   (@Call_Type, 
         @Date + @Time - [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]dateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, @Time), 0), 
         @Called_Number, 
         @Tag, 
         @Duration - [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, @Duration), 0), 
         @Line, 
         @Station, 
         @Account, 
         @Talk_Time - [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, @Talk_Time), 0))

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

notice that I'm doing a bunch of math on the time data passed in. All of that DateAdd... DateDiff stuff will essentially remove the time component (setting it to SQL Server's 'start date').

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bryandj23]

Sweet!!! Worked like a charm!!!

Thanks again!

 

[bryandj23]

Hopefully you're still around George...

Now that I've been populating my database, I'm attempting to query my table (in Enterprise Manager) to get all data logged yesterday. Unfortunately, I can't just plug in the DATE, because we added the time in there as well.

I'm assuming I'll have to do some sort of data conversion with dates in order to pull the data for a specific date?

My Date fields show: 6/7/2007 5:03:00 PM

 

[gmmastros]

Dates are a lot of fun. It really doesn't matter what the laguage is, either. Luckily, once you 'get it' you realize it's not all that bad either.

First, there is a trick to remove the time component from a DateTime value.

Open Query Analyzer and run this...

[tt][blue]Select GetDate()[/blue][/tt]

You will see today's date and time.

If you want to remove the time component, you can use this...

[tt][blue]Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)[/blue][/tt]

When you run this... you'll see that the time component is removed.

So... If you want to get all the records from yesterday, here is the way it should be done.

[COLOR=blue]Select[/color] Column1, Column2
[COLOR=blue]From[/color]   TableName
[COLOR=blue]Where[/color]  DateColumn >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), -1)
       And DateColumn < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom